﻿using AutoMapper;
using Dapper;
using DocumentFormat.OpenXml.Bibliography;
using DocumentFormat.OpenXml.Drawing;
using DocumentFormat.OpenXml.Drawing.Charts;
using DocumentFormat.OpenXml.Math;
using DocumentFormat.OpenXml.Office2013.Drawing.ChartStyle;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Wordprocessing;
using FFMpegCore.Enums;
using Hangfire.States;
using housingsafety.server.Caching.Core;
using housingsafety.server.Entities;
using housingsafety.server.Entities.DataModels;
using housingsafety.server.Helpers;
using housingsafety.server.Models;
using housingsafety.server.Repository;
using housingsafety.server.Svcs;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using Minio;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Npgsql;
using NPOI.POIFS.Crypt.Dsig;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using SixLabors.ImageSharp.ColorSpaces;
using StackExchange.Redis;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder;
using System.IO;
using System.Linq;
using System.Security.Policy;
using System.Text;
using System.Text.Json;
using System.Threading;
using static housingsafety.server.Controllers.VillageMngController;

namespace housingsafety.server.Controllers
{
    /// <summary>
    /// 房屋管理服务
    /// </summary>
    public class StaticController : BaseController<StaticController>
    {
        public IConfiguration Configuration { get; }
        private readonly IRepository<ZmVillageFireSafety> _villageFireSafetyRepository;
        private readonly ICache _cache;
        private readonly IMapper _mapper;
        private readonly IStorageProvider _storageProvider;
        private readonly ZmDbContext _dbContext;
        private readonly IRepository<ZmUser> _userRepository;
        private readonly IRepository<ZmBuildings> _buildingsRepository;
        private readonly IRepository<ZmBuildingsLayer> _buildingsLayerRepository;
        private readonly IRepository<ZmUpdateLog> _zmupdateRepository;
        private readonly IRepository<ZmUpdateLog> _zmupdate;
        private readonly IRepository<ZmFileAttach> _fileAttachRepository;
        private readonly IRepository<ZmPlaceCommercial> _placeCommercialRepository;
        private readonly IRepository<ZmPlaceResident> _placeResidentRepository;
        private readonly IOptions<StaticDataConf> _staticVtOptions;
        private readonly IRepository<ZmDepartment> _departmentRepository;
        private readonly IRepository<RiskCensusHouseYhpc> _riskHouseRepository;
        private readonly IRepository<RiskCensusYhpcUpldFile> _riskHouseFileRepository;
        private readonly IRepository<ZmPlaceResidentCheck> _placeResidentCheckRepository;
        private readonly IRepository<ZmPlaceCommercialCheck> _placeCommercialCheckRepository;
        private readonly IRepository<ZmYtPhoto> _zmYtPhotoRepository;
        private readonly IDbTransaction _transaction;

        private readonly Microsoft.AspNetCore.Http.Features.FormOptions _defaultFormOptions = new Microsoft.AspNetCore.Http.Features.FormOptions();
        private readonly string[] _permittedExtensions = { ".mp3", ".mp4", ".jpg", ".jpeg", ".png", ".geojson", ".doc", ".docx" };

        private static object _lock = new object();

        public StaticController(IStorageProvider storageProvider
            , IRepository<ZmDepartment> departmentRepository
            , IRepository<ZmUser> userRepository
              , IRepository<ZmVillageFireSafety> villageFireSafetyRepository
              , IRepository<ZmBuildings> buildingsRepository
            , IRepository<ZmBuildingsLayer> buildingsLayerRepository
            , IRepository<ZmFileAttach> fileAttachRepository
            , IRepository<ZmPlaceCommercial> placeCommercialRepository
            , IRepository<ZmPlaceResident> placeResidentRepository
            , IRepository<RiskCensusHouseYhpc> riskHouseRepository
            , IRepository<RiskCensusYhpcUpldFile> riskHouseFileRepository
            , IRepository<ZmPlaceResidentCheck> placeResidentCheckRepository
            , IRepository<ZmPlaceCommercialCheck> placeCommercialCheckRepository
            , IRepository<ZmYtPhoto> zmYtPhotoRepository
            , IMapper mapper, ICacheProvider cacheProvider, ZmDbContext dbContext
            , IOptions<StaticDataConf> staticVtOptions
            , ILogger<StaticController> logger,
            IConfiguration configuration
            , IRepository<ZmSystemLog> systemLogRepository, IRepository<ZmUpdateLog> zmupdateRepository) : base(logger, systemLogRepository)
        {
            _zmupdateRepository = zmupdateRepository;
            _storageProvider = storageProvider;
            _mapper = mapper;
            _dbContext = dbContext;
            _cache = cacheProvider.CreateCache();
            _zmYtPhotoRepository = zmYtPhotoRepository;
            _userRepository = userRepository;
            _buildingsRepository = buildingsRepository;
            _buildingsLayerRepository = buildingsLayerRepository;
            _fileAttachRepository = fileAttachRepository;
            _placeCommercialRepository = placeCommercialRepository;
            _placeResidentRepository = placeResidentRepository;
            _staticVtOptions = staticVtOptions;
            _departmentRepository = departmentRepository;
            _riskHouseRepository = riskHouseRepository;
            _riskHouseFileRepository = riskHouseFileRepository;
            _placeResidentCheckRepository = placeResidentCheckRepository;
            _placeCommercialCheckRepository = placeCommercialCheckRepository;
            Configuration = configuration;
            _villageFireSafetyRepository = villageFireSafetyRepository;
            _zmupdateRepository = zmupdateRepository;

        }


        /// <summary>
        /// 人员疏散通道统计
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("static/stat/sysstdlist")]
        public RespRet<Page<object>> baseInfoDetaile( [FromQuery] int pageIndex, [FromQuery] int limit, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string cked)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "";
            if (model != null)
            {
                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                }
                else if (user.RoleType == 3) //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else  //乡镇
                {
                    strWhere += "and xzqdm='" + model.RegionCode.Substring(0, 6) + "' ";
                }
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = ""+startTime+"";
                endTime = DateTime.Now.ToString("yyyy-MM-dd");
            }

            string tablename = "zm_pc_baselog";
            string partition = " ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,";
            string row_number = "  AND row_num = 1 ";
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            if (startTime == "2024-01-01" && endTime == today) 
            {
                tablename = "zm_pc_base";
                partition = "";
                row_number = "";
            }

            string sql = "  select ROW_NUMBER() OVER()index ,* from(select t2.*, ssltdbfjsl, COALESCE(three, 0) three, " +
                "COALESCE(two, 0) two, COALESCE(one, 0) one, COALESCE(three_ywccount, 0) three_ywccount, COALESCE(two_ywccount, 0) two_ywccount," +
                " COALESCE(one_ywccount, 0) one_ywccount," +
                "case  when  ( zcount - ssltdbfjsl )<0 then 0  else zcount - ssltdbfjsl end  wwccount," +
               " CASE    WHEN one_ywccount = 0 THEN'0.00%' ELSE concat(CASE WHEN (round((((one_ywccount ) ::NUMERIC / (one) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) > (100) :: NUMERIC ) THEN" +
               " 100.00 ELSE round((((one_ywccount ) ::NUMERIC / (one) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) END,'%' ) END one_pencent," +
               " CASE    WHEN two_ywccount = 0 THEN'0.00%' ELSE concat(CASE WHEN (round((((two_ywccount ) ::NUMERIC / (two) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) > (100) :: NUMERIC ) THEN" +
               " 100.00 ELSE round((((two_ywccount ) ::NUMERIC / (two) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) END,'%' ) END two_pencent," +
               " CASE    WHEN three_ywccount = 0 THEN'0.00%' ELSE concat(CASE WHEN (round((((three_ywccount ) ::NUMERIC / (three) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) > (100) :: NUMERIC ) THEN" +
               " 100.00 ELSE round((((three_ywccount ) ::NUMERIC / (three) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) END,'%' ) END three_pencent," +
               "     CASE    WHEN ssltdbfjsl = 0 THEN'0.00%' ELSE concat(CASE WHEN (round((((ssltdbfjsl ) ::NUMERIC / (zcount) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) > (100) :: NUMERIC ) THEN" +
               " 100.00 ELSE round((((ssltdbfjsl ) ::NUMERIC / (zcount) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) END,'%' ) END dbl_pencent" +
                " from(SELECT  sum(case when tdsfdb= '是' then 1 else 0 end) AS ssltdbfjsl," +
                " sum( CASE WHEN(zm_pc_base.cs >= 3) THEN 1 ELSE 0 END) AS three," +
                " sum( CASE WHEN(zm_pc_base.cs = 2) THEN 1 ELSE 0 END) AS two," +
                " sum( CASE WHEN(zm_pc_base.cs <= 1) THEN 1 ELSE 0 END) AS one," +
                " sum(case when tdsfdb= '是' and cs>= 3 then 1 else 0 end) AS three_ywccount," +
                " sum(case when tdsfdb= '是' and cs = 2 then 1 else 0 end) AS two_ywccount," +
                "    sum(case when tdsfdb= '是' and cs = 1 then 1 else 0 end) AS one_ywccount," +
                "    zm_pc_base.xzq,zm_pc_base.xzqdm FROM" +
                " (SELECT  "+ partition + " t1.*,jcfwzt,hcfwzt FROM " + tablename + " t1  JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh LEFT JOIN  (select fwbh,fwzt jcfwzt from  zm_pc_base_cj ) t2 ON t1.fwbh :: TEXT = t2.fwbh :: TEXT left join  (select fwbh,fwzt hcfwzt from  zm_pc_base_sqjz where yhly='4') t3 on t1.fwbh = t3.fwbh " +
                "    WHERE t1.create_on BETWEEN '"+startTime+" 00:00:00' AND '"+endTime+" 23:59:59'  ) zm_pc_base" +
                "    WHERE (create_on) BETWEEN '"+startTime+" 00:00:00' AND '"+ endTime + " 23:59:59' "+ row_number + "  AND jcfwzt IS NULL  and hcfwzt is null  " +
                "    GROUP BY zm_pc_base.xzqdm,	zm_pc_base.xzq) t1" +
                "    LEFT JOIN(	 SELECT COUNT(1) AS zcount, zm_buildings.xzq, zm_buildings.xzqdm FROM(SELECT t1.*, t2.fwzt,t3.fwzt hcfwzt FROM zm_buildings t1 LEFT JOIN  (select fwbh,fwzt from  zm_pc_base_cj ) t2 ON t1.fwbh :: TEXT = t2.fwbh :: TEXT left join  (select fwbh,fwzt from  zm_pc_base_sqjz where yhly='4') t3 on t1.fwbh = t3.fwbh) zm_buildings WHERE fwzt IS NULL and hcfwzt is null GROUP BY zm_buildings.xzqdm, zm_buildings.xzq ORDER BY zm_buildings.xzqdm   " +
                "    ) t2 ON t1.xzqdm = t2.xzqdm) t where 1 = 1  " + strWhere + "   limit " + limit + " offset " + limit * pageIndex;                                                                                                                                                                  
                                                                                                                                                                                                                                                                                           
         
            string sql_count = "  select  count(1) cnt from(select t2.*, ssltdbfjsl, COALESCE(three, 0) three, " +
                "COALESCE(two, 0) two, COALESCE(one, 0) one, COALESCE(three_ywccount, 0) three_ywccount, COALESCE(two_ywccount, 0) two_ywccount," +
               " COALESCE(one_ywccount, 0) one_ywccount," +
                 "case  when  ( zcount - ssltdbfjsl )<0 then 0  else zcount - ssltdbfjsl end  wwccount," +
               " CASE    WHEN one_ywccount = 0 THEN'0.00%' ELSE concat(CASE WHEN (round((((one_ywccount ) ::NUMERIC / (one) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) > (100) :: NUMERIC ) THEN" +
               " 100.00 ELSE round((((one_ywccount ) ::NUMERIC / (one) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) END,'%' ) END one_pencent," +
               " CASE    WHEN two_ywccount = 0 THEN'0.00%' ELSE concat(CASE WHEN (round((((two_ywccount ) ::NUMERIC / (two) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) > (100) :: NUMERIC ) THEN" +
               " 100.00 ELSE round((((two_ywccount ) ::NUMERIC / (two) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) END,'%' ) END two_pencent," +
               " CASE    WHEN three_ywccount = 0 THEN'0.00%' ELSE concat(CASE WHEN (round((((three_ywccount ) ::NUMERIC / (three) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) > (100) :: NUMERIC ) THEN" +
               " 100.00 ELSE round((((three_ywccount ) ::NUMERIC / (three) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) END,'%' ) END three_pencent," +
               "     CASE    WHEN ssltdbfjsl = 0 THEN'0.00%' ELSE concat(CASE WHEN (round((((ssltdbfjsl ) ::NUMERIC / (zcount) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) > (100) :: NUMERIC ) THEN" +
               " 100.00 ELSE round((((ssltdbfjsl ) ::NUMERIC / (zcount) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) END,'%' ) END dbl_pencent" +
                " from(SELECT  sum(case when tdsfdb= '是' then 1 else 0 end) AS ssltdbfjsl," +
                " sum( CASE WHEN(zm_pc_base.cs >= 3) THEN 1 ELSE 0 END) AS three," +
                " sum( CASE WHEN(zm_pc_base.cs = 2) THEN 1 ELSE 0 END) AS two," +
                " sum( CASE WHEN(zm_pc_base.cs <= 1) THEN 1 ELSE 0 END) AS one," +
                " sum(case when tdsfdb= '是' and cs>= 3 then 1 else 0 end) AS three_ywccount," +
                " sum(case when tdsfdb= '是' and cs = 2 then 1 else 0 end) AS two_ywccount," +
                "    sum(case when tdsfdb= '是' and cs = 1 then 1 else 0 end) AS one_ywccount," +
                "    zm_pc_base.xzq,zm_pc_base.xzqdm FROM" +
                " (SELECT "+ partition + " t1.* ,jcfwzt,hcfwzt FROM " + tablename + " t1  JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh LEFT JOIN  (select fwbh,fwzt jcfwzt from  zm_pc_base_cj ) t2 ON t1.fwbh :: TEXT = t2.fwbh :: TEXT left join  (select fwbh,fwzt hcfwzt from  zm_pc_base_sqjz where yhly='4') t3 on t1.fwbh = t3.fwbh" +
                "    WHERE t1.create_on BETWEEN '" + startTime + " 00:00:00' AND '" + endTime + " 23:59:59'  ) zm_pc_base" +
                "    WHERE (create_on) BETWEEN '" + startTime + " 00:00:00' AND '" + endTime + " 23:59:59' "+ row_number + " AND jcfwzt IS NULL  and hcfwzt is null " +
                "    GROUP BY zm_pc_base.xzqdm,	zm_pc_base.xzq) t1" +
                "    LEFT JOIN(	SELECT COUNT(1) AS zcount, zm_buildings.xzq, zm_buildings.xzqdm FROM(SELECT t1.*, t2.fwzt,t3.fwzt hcfwzt FROM zm_buildings t1 LEFT JOIN  (select fwbh,fwzt from  zm_pc_base_cj ) t2 ON t1.fwbh :: TEXT = t2.fwbh :: TEXT left join (select fwbh,fwzt from  zm_pc_base_sqjz where yhly='4') t3 on t1.fwbh = t3.fwbh) zm_buildings WHERE fwzt IS NULL and hcfwzt is null GROUP BY zm_buildings.xzqdm, zm_buildings.xzq ORDER BY zm_buildings.xzqdm   " +
                "    ) t2 ON t1.xzqdm = t2.xzqdm) t where 1 = 1  " + strWhere;


            if (cked == "true")
            {
                sql = @"select  ROW_NUMBER() OVER ()index ,* from (select concat(t1.village, '-', t1.townold,'-',t1.xzq) xzq,t2.towndm,t2.villagedm,t2.xzqdm,
              COALESCE(zcount,0)zcount, COALESCE(ssltdbfjsl,0)ssltdbfjsl,COALESCE(wwccount,0)wwccount,dbl_pencent,one_pencent,two_pencent,three_pencent from zdcmd t1
            left join (select t2.*, ssltdbfjsl, COALESCE(three, 0) three, " +
                "COALESCE(two, 0) two, COALESCE(one, 0) one, COALESCE(three_ywccount, 0) three_ywccount, COALESCE(two_ywccount, 0) two_ywccount," +
                " COALESCE(one_ywccount, 0) one_ywccount," +
                "case  when  ( zcount - ssltdbfjsl )<0 then 0  else zcount - ssltdbfjsl end  wwccount," +
               " CASE    WHEN one_ywccount = 0 THEN'0.00%' ELSE concat(CASE WHEN (round((((one_ywccount ) ::NUMERIC / (one) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) > (100) :: NUMERIC ) THEN" +
               " 100.00 ELSE round((((one_ywccount ) ::NUMERIC / (one) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) END,'%' ) END one_pencent," +
              " CASE    WHEN two_ywccount = 0 THEN'0.00%' ELSE concat(CASE WHEN (round((((two_ywccount ) ::NUMERIC / (two) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) > (100) :: NUMERIC ) THEN" +
               " 100.00 ELSE round((((two_ywccount ) ::NUMERIC / (two) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) END,'%' ) END two_pencent," +
               " CASE    WHEN three_ywccount = 0 THEN'0.00%' ELSE concat(CASE WHEN (round((((three_ywccount ) ::NUMERIC / (three) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) > (100) :: NUMERIC ) THEN" +
               " 100.00 ELSE round((((three_ywccount ) ::NUMERIC / (three) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) END,'%' ) END three_pencent," +
               "     CASE    WHEN ssltdbfjsl = 0 THEN'0.00%' ELSE concat(CASE WHEN (round((((ssltdbfjsl ) ::NUMERIC / (zcount) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) > (100) :: NUMERIC ) THEN" +
               " 100.00 ELSE round((((ssltdbfjsl ) ::NUMERIC / (zcount) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) END,'%' ) END dbl_pencent" +
                " from(SELECT  sum(case when tdsfdb= '是' then 1 else 0 end) AS ssltdbfjsl," +
                " sum( CASE WHEN(zm_pc_base.cs >= 3) THEN 1 ELSE 0 END) AS three," +
                " sum( CASE WHEN(zm_pc_base.cs = 2) THEN 1 ELSE 0 END) AS two," +
                " sum( CASE WHEN(zm_pc_base.cs <= 1) THEN 1 ELSE 0 END) AS one," +
                " sum(case when tdsfdb= '是' and cs>= 3 then 1 else 0 end) AS three_ywccount," +
                " sum(case when tdsfdb= '是' and cs = 2 then 1 else 0 end) AS two_ywccount," +
                "    sum(case when tdsfdb= '是' and cs = 1 then 1 else 0 end) AS one_ywccount," +
                "    zm_pc_base.village,town,xzqdm FROM" +
                " (SELECT "+partition+ " t1.* ,jcfwzt,hcfwzt FROM " + tablename+ " t1  JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh LEFT JOIN  (select fwbh,fwzt jcfwzt from  zm_pc_base_cj ) t2 ON t1.fwbh :: TEXT = t2.fwbh :: TEXT left join  (select fwbh,fwzt hcfwzt from  zm_pc_base_sqjz where yhly='4') t3 on t1.fwbh = t3.fwbh" +
                "    WHERE t1.create_on BETWEEN '"+startTime+" 00:00:00' AND '"+endTime+" 23:59:59'  ) zm_pc_base" +
                "    WHERE (create_on) BETWEEN '"+startTime+" 00:00:00' AND '"+ endTime + " 23:59:59' "+ row_number + " AND jcfwzt IS NULL  and hcfwzt is null" +
                "    GROUP BY zm_pc_base.village,town,xzqdm) t1" +
                "    LEFT JOIN(SELECT COUNT(1) AS zcount, zm_buildings.villagedm,towndm,xzqdm  FROM" +
                "     (SELECT t1.*, t2.fwzt,t3.fwzt hcfwzt FROM zm_buildings t1 LEFT JOIN (select fwbh,fwzt from  zm_pc_base_cj)  t2 ON t1.fwbh :: TEXT = t2.fwbh :: TEXT left join (select fwbh,fwzt from  zm_pc_base_sqjz where yhly='4') t3 on t1.fwbh = t3.fwbh) zm_buildings" +
                "    WHERE fwzt IS NULL  and hcfwzt is null GROUP BY zm_buildings.villagedm,towndm,xzqdm ORDER BY zm_buildings.villagedm" +
                "    ) t2 ON t1.village = t2.villagedm) t2 on t1.villagedm=t2.villagedm " +
               ") t where 1=1  order by villagedm ";

                sql_count = @"select  count(1) cnt from (select concat(t1.village, '-', t1.townold,'-',t1.xzq) xzq,t2.towndm,t2.villagedm,t2.xzqdm,
              COALESCE(zcount,0)zcount, COALESCE(ssltdbfjsl,0)ssltdbfjsl,COALESCE(wwccount,0)wwccount,dbl_pencent,one_pencent,two_pencent,three_pencent from zdcmd t1
            left join (select t2.*, ssltdbfjsl, COALESCE(three, 0) three, " +
            "COALESCE(two, 0) two, COALESCE(one, 0) one, COALESCE(three_ywccount, 0) three_ywccount, COALESCE(two_ywccount, 0) two_ywccount," +
            " COALESCE(one_ywccount, 0) one_ywccount," +
            "case  when  ( zcount - ssltdbfjsl )<0 then 0  else zcount - ssltdbfjsl end  wwccount," +
           " CASE    WHEN one_ywccount = 0 THEN'0.00%' ELSE concat(CASE WHEN (round((((one_ywccount ) ::NUMERIC / (one) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) > (100) :: NUMERIC ) THEN" +
           " 100.00 ELSE round((((one_ywccount ) ::NUMERIC / (one) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) END,'%' ) END one_pencent," +
          " CASE    WHEN two_ywccount = 0 THEN'0.00%' ELSE concat(CASE WHEN (round((((two_ywccount ) ::NUMERIC / (two) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) > (100) :: NUMERIC ) THEN" +
           " 100.00 ELSE round((((two_ywccount ) ::NUMERIC / (two) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) END,'%' ) END two_pencent," +
           " CASE    WHEN three_ywccount = 0 THEN'0.00%' ELSE concat(CASE WHEN (round((((three_ywccount ) ::NUMERIC / (three) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) > (100) :: NUMERIC ) THEN" +
           " 100.00 ELSE round((((three_ywccount ) ::NUMERIC / (three) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) END,'%' ) END three_pencent," +
           "     CASE    WHEN ssltdbfjsl = 0 THEN'0.00%' ELSE concat(CASE WHEN (round((((ssltdbfjsl ) ::NUMERIC / (zcount) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) > (100) :: NUMERIC ) THEN" +
           " 100.00 ELSE round((((ssltdbfjsl ) ::NUMERIC / (zcount) ::NUMERIC ) *(100) :: NUMERIC ), 2 ) END,'%' ) END dbl_pencent" +
            " from(SELECT  sum(case when tdsfdb= '是' then 1 else 0 end) AS ssltdbfjsl," +
            " sum( CASE WHEN(zm_pc_base.cs >= 3) THEN 1 ELSE 0 END) AS three," +
            " sum( CASE WHEN(zm_pc_base.cs = 2) THEN 1 ELSE 0 END) AS two," +
            " sum( CASE WHEN(zm_pc_base.cs <= 1) THEN 1 ELSE 0 END) AS one," +
            " sum(case when tdsfdb= '是' and cs>= 3 then 1 else 0 end) AS three_ywccount," +
            " sum(case when tdsfdb= '是' and cs = 2 then 1 else 0 end) AS two_ywccount," +
            "    sum(case when tdsfdb= '是' and cs = 1 then 1 else 0 end) AS one_ywccount," +
            "    zm_pc_base.village,town,xzqdm FROM" +
            " (SELECT "+ partition + " t1.* ,jcfwzt,hcfwzt FROM " + tablename+ " t1  JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh LEFT JOIN  (select fwbh,fwzt jcfwzt from  zm_pc_base_cj ) t2 ON t1.fwbh :: TEXT = t2.fwbh :: TEXT left join  (select fwbh,fwzt hcfwzt from  zm_pc_base_sqjz where yhly='4') t3 on t1.fwbh = t3.fwbh" +
            "    WHERE t1.create_on BETWEEN '" + startTime + " 00:00:00' AND '" + endTime + " 23:59:59' ) zm_pc_base" +
            "    WHERE (create_on) BETWEEN '" + startTime + " 00:00:00' AND '" + endTime + " 23:59:59' "+ row_number + " AND jcfwzt IS NULL  and hcfwzt is null " +
            "    GROUP BY zm_pc_base.village,town,xzqdm) t1" +
            "    LEFT JOIN(SELECT COUNT(1) AS zcount, zm_buildings.villagedm ,towndm,xzqdm FROM " +
            " (SELECT t1.*, t2.fwzt,t3.fwzt hcfwzt FROM zm_buildings t1 LEFT JOIN (select fwbh,fwzt from  zm_pc_base_cj) t2 ON t1.fwbh :: TEXT = t2.fwbh :: TEXT left join (select fwbh,fwzt from  zm_pc_base_sqjz where yhly='4') t3 on t1.fwbh = t3.fwbh) zm_buildings " +
            " WHERE fwzt IS NULL and hcfwzt is null GROUP BY zm_buildings.villagedm,towndm,xzqdm ORDER BY zm_buildings.villagedm        " +
            "    ) t2 ON t1.village = t2.villagedm) t2 on t1.villagedm=t2.villagedm " +
           ") t where 1=1 ";

            }

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
      || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
      || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }

      


        /// <summary>
        /// 专项信息统计
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("static/specialBaseStatic/list")]
        public RespRet<Page<object>> getSpecialBaseStaticList([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string cked)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            string strWhere = "";
            if (!string.IsNullOrEmpty(xzqdm + ""))
            {
                strWhere += "and xzqdm ='" + xzqdm + "'";
            }
            if (!string.IsNullOrEmpty(towndm + ""))
            {
                strWhere += "and towndm ='" + towndm + "'";
            }
            if (!string.IsNullOrEmpty(villagedm + ""))
            {
                strWhere += "and villagedm ='" + villagedm + "'";
            }
            if (string.IsNullOrEmpty(startTime))
            {
                startTime = ""+startTime+" ";
                endTime = DateTime.Now.ToString("yyyy-MM-dd");
            }
            string tablename = "zm_pc_baselog";
            string row_filed = "";
            string row_num_where = "";
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            if (startTime == "2024-01-01" && endTime == today)
            {
                tablename = "zm_pc_base";
                row_filed = "";
                row_num_where = "";
            }
            else 
            {
                DateTime start = Convert.ToDateTime(startTime);
                DateTime end = Convert.ToDateTime(endTime);
                if (start.Month == end.Month)
                {
                    tablename = "zm_pc_baselog_" + start.Month;

                }
                row_filed = " ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num, ";
                row_num_where = " and row_num =1 ";
            }
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }

            string sql = @"select ROW_NUMBER() OVER() index,* from (select mm.xzq,mm.village,mm.town ,COALESCE(zcount,0) zcount,COALESCE(zg_ddc_num,0) zg_ddc_num,COALESCE(zg_yhsyq_num,0) zg_yhsyq_num,
COALESCE(ccfhwggptzlsl,0) ccfhwggptzlsl,COALESCE(xggdjsbpds,0) xggdjsbpds,COALESCE(ssltdbfjsl,0) ssltdbfjsl,COALESCE(no_ssltdbfjsl,0) no_ssltdbfjsl,
COALESCE(dbl_pencent,'0%')dbl_pencent,COALESCE(azl_pencent,'0%') azl_pencent from zm_building_ledger mm INNER JOIN (select b4.*,pp.region_name village ,kk.region_name town,COALESCE(xggdjsbpds,0) xggdjsbpds ,
COALESCE(ssltdbfjsl,0) ssltdbfjsl,case when  (zcount-COALESCE(ssltdbfjsl,0)) <=0 then 0 else (zcount-COALESCE(ssltdbfjsl,0)) end  
         no_ssltdbfjsl,case when ssltdbfjsl= 0 then '0.00%' else concat(  CASE WHEN(round((((ssltdbfjsl)::numeric / (zcount)::numeric) * (100)::numeric), 2) > (100)::numeric)
   THEN 100.00 ELSE round((((ssltdbfjsl)::numeric / (zcount)::numeric) * (100)::numeric), 2)      END, '%') end dbl_pencent,case when xggdjsbpds= 0 then '0.00%' else
     concat( CASE WHEN(round((((xggdjsbpds)::numeric / (zcount)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00 ELSE round((((xggdjsbpds)::numeric / (zcount)::numeric) * (100)::numeric), 2)      END, '%') end azl_pencent 
from (select b1.*,COALESCE(zg_ddc_num,0)zg_ddc_num,COALESCE(zg_yhsyq_num,0) zg_yhsyq_num,
   COALESCE(ccfhwggptzlsl,0)ccfhwggptzlsl from (SELECT count(1) AS zcount,zm_buildings.xzq,zm_buildings.xzqdm,zm_buildings.towndm,zm_buildings.villagedm  
FROM (select  t1.xzq,t1.xzqdm,t1.towndm,t1.villagedm ,t2.fwzt,t3.fwzt hcfwzt from zm_buildings t1 LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh::text = t2.fwbh::text 
LEFT JOIN (select fwbh,fwzt from zm_pc_base_sqjz where fwzt is not null and yhly='4') t3 ON t1.fwbh::text = t3.fwbh::text) zm_buildings  where fwzt is null and hcfwzt is null
GROUP BY zm_buildings.xzqdm, zm_buildings.xzq,zm_buildings.towndm,zm_buildings.villagedm ORDER BY zm_buildings.xzqdm,zm_buildings.towndm,zm_buildings.villagedm) b1  
left join( select (COALESCE(zc_zg_ddc_num,0)+COALESCE(jc_zg_ddc_num,0)) zg_ddc_num,(COALESCE(zc_zg_yhsyq_num,0)+COALESCE(jc_zg_yhsyq_num,0))zg_yhsyq_num,a.villagedm from  
	(select sum(zg_ddc_num) zc_zg_ddc_num,sum(zg_yhsyq_num) zc_zg_yhsyq_num,b.villagedm from zm_pc_yhlog_dxzgson     a join zm_buildings b on a.fwbh=b.fwbh 
left join zm_pc_base_cj c on b.fwbh=c.fwbh LEFT JOIN (select fwbh,fwzt hcfwzt from zm_pc_base_sqjz where fwzt is not null and yhly='4') t3 ON b.fwbh::text = t3.fwbh::text where fwzt is null and hcfwzt is null and a.create_on between '" + startTime+"  00:00:00' and '"+endTime+" 23:59:59'  GROUP BY b.villagedm) a "
+ "left join (select sum(zg_ddc_num) jc_zg_ddc_num,sum(zg_yhsyq_num) jc_zg_yhsyq_num,b.villagedm from zm_pc_yhlog_dxzgson_cj     a join zm_buildings b on a.fwbh=b.fwbh   "
+ "join zm_pc_yhlog_dx_cj c on a.yhlog_dx_id=c.id join zm_pc_yh_cj d on d.id=c.yhlog_id  where d.jcly='5' and a.create_on between '"+startTime+" 00:00:00' and '"+endTime+ " 23:59:59'  GROUP BY b.villagedm)b "
+ "on a.villagedm=b.villagedm) b2	on b1.villagedm=b2.villagedm  left join (select sum(ccfhwggptzlsl) ccfhwggptzlsl,villagedm from zm_pc_gjzz_cjlog where date(create_on)   "
+ "between '"+startTime+ "  00:00:00' and '"+ endTime + " 23:59:59' group by villagedm) b3 on b1.villagedm=b3.villagedm) b4  left join (SELECT count(DISTINCT CASE WHEN (zm_pc_base.xggdjsbp > 0)  "
+ "THEN zm_pc_base.fwbh ELSE NULL::character varying END) AS xggdjsbpds, sum( CASE WHEN tdsfdb='是' THEN 1 ELSE 0 END) AS ssltdbfjsl,zm_pc_base.xzq,zm_pc_base.xzqdm,zm_pc_base.town, "
+ "zm_pc_base.village FROM (SELECT  "+row_filed+" t1.*       FROM "+tablename+" t1  JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh    "
+ "WHERE t1.create_on BETWEEN '" + startTime+"  00:00:00' AND '"+endTime+ " 23:59:59'    )zm_pc_base WHERE    fwbh not in( select fwbh  from  zm_pc_base_cj where fwzt is not null)    AND fwbh not in( select fwbh  from  zm_pc_base_sqjz where fwzt is not null and yhly = '4') "
+ "and fwbh not in (select fwbh from zm_pc_base_sqjz where fwzt is not null and yhly='4')  "+ row_num_where+"  GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq,zm_pc_base.town,zm_pc_base.village) b5 on b4.villagedm=b5.village  "
+ "left join zm_department pp on b4.villagedm=pp.id left join zm_department kk on b4.towndm=kk.id order by b4.villagedm ) kk on mm.villagedm=kk.villagedm) t where 1=1  "+strWhere+" limit "+ pagesize + " offset ("+ pageindex + " - 1) * "+pagesize; 


            string sql_count  = @"select count(1) cnt from (select mm.xzq,mm.village,mm.town,COALESCE(zcount,0) zcount,COALESCE(zg_ddc_num,0) zg_ddc_num,COALESCE(zg_yhsyq_num,0) zg_yhsyq_num,
COALESCE(ccfhwggptzlsl,0) ccfhwggptzlsl,COALESCE(xggdjsbpds,0) xggdjsbpds,COALESCE(ssltdbfjsl,0) ssltdbfjsl,COALESCE(no_ssltdbfjsl,0) no_ssltdbfjsl,
COALESCE(dbl_pencent,'0%')dbl_pencent,COALESCE(azl_pencent,'0%') azl_pencent from zm_building_ledger mm INNER JOIN (select b4.*,pp.region_name village ,kk.region_name town,COALESCE(xggdjsbpds,0) xggdjsbpds ,
COALESCE(ssltdbfjsl,0) ssltdbfjsl,case when  (zcount-COALESCE(ssltdbfjsl,0)) <=0 then 0 else (zcount-COALESCE(ssltdbfjsl,0)) end  
         no_ssltdbfjsl,case when ssltdbfjsl= 0 then '0.00%' else concat(  CASE WHEN(round((((ssltdbfjsl)::numeric / (zcount)::numeric) * (100)::numeric), 2) > (100)::numeric)
   THEN 100.00 ELSE round((((ssltdbfjsl)::numeric / (zcount)::numeric) * (100)::numeric), 2)      END, '%') end dbl_pencent,case when xggdjsbpds= 0 then '0.00%' else
     concat( CASE WHEN(round((((xggdjsbpds)::numeric / (zcount)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00 ELSE round((((xggdjsbpds)::numeric / (zcount)::numeric) * (100)::numeric), 2)      END, '%') end azl_pencent 
from (select b1.*,COALESCE(zg_ddc_num,0)zg_ddc_num,COALESCE(zg_yhsyq_num,0) zg_yhsyq_num,
   COALESCE(ccfhwggptzlsl,0)ccfhwggptzlsl from (SELECT count(1) AS zcount,zm_buildings.xzq,zm_buildings.xzqdm,zm_buildings.towndm,zm_buildings.villagedm  
FROM (select  t1.xzq,t1.xzqdm,t1.towndm,t1.villagedm ,t2.fwzt,t3.fwzt hcfwzt from zm_buildings t1 LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh::text = t2.fwbh::text 
LEFT JOIN (select fwbh,fwzt from zm_pc_base_sqjz where fwzt is not null and yhly='4') t3 ON t1.fwbh::text = t3.fwbh::text) zm_buildings  where fwzt is null and hcfwzt is null
GROUP BY zm_buildings.xzqdm, zm_buildings.xzq,zm_buildings.towndm,zm_buildings.villagedm ORDER BY zm_buildings.xzqdm,zm_buildings.towndm,zm_buildings.villagedm) b1  
left join( select (COALESCE(zc_zg_ddc_num,0)+COALESCE(jc_zg_ddc_num,0)) zg_ddc_num,(COALESCE(zc_zg_yhsyq_num,0)+COALESCE(jc_zg_yhsyq_num,0))zg_yhsyq_num,a.villagedm from  
	(select sum(zg_ddc_num) zc_zg_ddc_num,sum(zg_yhsyq_num) zc_zg_yhsyq_num,b.villagedm from zm_pc_yhlog_dxzgson     a join zm_buildings b on a.fwbh=b.fwbh 
left join zm_pc_base_cj c on b.fwbh=c.fwbh LEFT JOIN (select fwbh,fwzt hcfwzt from zm_pc_base_sqjz where fwzt is not null and yhly='4') t3 ON b.fwbh::text = t3.fwbh::text where fwzt is null and hcfwzt is null and a.create_on between '" + startTime + "  00:00:00' and '" + endTime + " 23:59:59'  GROUP BY b.villagedm) a "
+ "left join (select sum(zg_ddc_num) jc_zg_ddc_num,sum(zg_yhsyq_num) jc_zg_yhsyq_num,b.villagedm from zm_pc_yhlog_dxzgson_cj     a join zm_buildings b on a.fwbh=b.fwbh   "
+ "join zm_pc_yhlog_dx_cj c on a.yhlog_dx_id=c.id join zm_pc_yh_cj d on d.id=c.yhlog_id  where d.jcly='5' and a.create_on between '" + startTime + " 00:00:00' and '" + endTime + " 23:59:59'  GROUP BY b.villagedm)b "
+ "on a.villagedm=b.villagedm) b2	on b1.villagedm=b2.villagedm  left join (select sum(ccfhwggptzlsl) ccfhwggptzlsl,villagedm from zm_pc_gjzz_cjlog where date(create_on)   "
+ "between '" + startTime + "  00:00:00' and '"+ endTime + " 23:59:59' group by villagedm) b3 on b1.villagedm=b3.villagedm) b4  left join (SELECT count(DISTINCT CASE WHEN (zm_pc_base.xggdjsbp > 0)  "
+ "THEN zm_pc_base.fwbh ELSE NULL::character varying END) AS xggdjsbpds, sum( CASE WHEN tdsfdb='是' THEN 1 ELSE 0 END) AS ssltdbfjsl,zm_pc_base.xzq,zm_pc_base.xzqdm,zm_pc_base.town, "
+ "zm_pc_base.village FROM (SELECT  " + row_filed + " t1.*       FROM " + tablename + " t1  JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh    "
+ "WHERE t1.create_on BETWEEN '" + startTime + "  00:00:00' AND '" + endTime + " 23:59:59'    )zm_pc_base WHERE    fwbh not in( select fwbh  from  zm_pc_base_cj where fwzt is not null)    AND fwbh not in( select fwbh  from  zm_pc_base_sqjz where fwzt is not null and yhly = '4') "
+ "and fwbh not in (select fwbh from zm_pc_base_sqjz where fwzt is not null and yhly='4')  " + row_num_where + "  GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq,zm_pc_base.town,zm_pc_base.village) b5 on b4.villagedm=b5.village  "
+ "left join zm_department pp on b4.villagedm=pp.id left join zm_department kk on b4.towndm=kk.id order by b4.villagedm ) kk on mm.villagedm=kk.villagedm) t where 1=1  " + strWhere  ;

                if (cked == "true") 
            {
               sql = @"select  ROW_NUMBER() OVER() index,* from (select mm.xzq,mm.village,mm.town,COALESCE(zcount,0) zcount,COALESCE(zg_ddc_num,0) zg_ddc_num,
COALESCE(zg_yhsyq_num,0) zg_yhsyq_num,COALESCE(ccfhwggptzlsl,0) ccfhwggptzlsl,COALESCE(xggdjsbpds,0) xggdjsbpds,COALESCE(ssltdbfjsl,0) ssltdbfjsl,
COALESCE(no_ssltdbfjsl,0) no_ssltdbfjsl,COALESCE(dbl_pencent,'0%')dbl_pencent,COALESCE(azl_pencent,'0%') azl_pencent from zm_building_ledger mm INNER JOIN
(select b4.*,pp.region_name village ,kk.region_name town,COALESCE(xggdjsbpds,0) xggdjsbpds ,COALESCE(ssltdbfjsl,0) ssltdbfjsl,case when 
(zcount-COALESCE(ssltdbfjsl,0)) <=0 then 0 else (zcount-COALESCE(ssltdbfjsl,0)) end  
         no_ssltdbfjsl,case when ssltdbfjsl= 0 then '0.00%' else concat(  CASE WHEN(round((((ssltdbfjsl)::numeric / (zcount)::numeric) * (100)::numeric), 2) > (100)::numeric)
   THEN 100.00 ELSE round((((ssltdbfjsl)::numeric / (zcount)::numeric) * (100)::numeric), 2)      END, '%') end dbl_pencent,case when xggdjsbpds= 0 then '0.00%' else
     concat( CASE WHEN(round((((xggdjsbpds)::numeric / (zcount)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00 ELSE round((((xggdjsbpds)::numeric / (zcount)::numeric) * (100)::numeric), 2)      END, '%') end azl_pencent 
from (select b1.*,COALESCE(zg_ddc_num,0)zg_ddc_num,COALESCE(zg_yhsyq_num,0) zg_yhsyq_num,
   COALESCE(ccfhwggptzlsl,0)ccfhwggptzlsl from (SELECT count(1) AS zcount,zm_buildings.xzq,zm_buildings.xzqdm,zm_buildings.towndm,zm_buildings.villagedm  FROM (select  t1.xzq,t1.xzqdm,t1.towndm,t1.villagedm ,fwzt,t1.fwbh from zm_buildings t1 LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh::text = t2.fwbh::text) zm_buildings  
where  fwzt is null and fwbh not in( select fwbh  from  zm_pc_base_sqjz where fwzt is not null and yhly='4') GROUP BY zm_buildings.xzqdm, zm_buildings.xzq,zm_buildings.towndm,zm_buildings.villagedm ORDER BY zm_buildings.xzqdm,zm_buildings.towndm,zm_buildings.villagedm) b1  left join( select (COALESCE(zc_zg_ddc_num,0)+COALESCE(jc_zg_ddc_num,0)) zg_ddc_num,
(COALESCE(zc_zg_yhsyq_num,0)+COALESCE(jc_zg_yhsyq_num,0))zg_yhsyq_num,a.villagedm from  
	(select sum(zg_ddc_num) zc_zg_ddc_num,sum(zg_yhsyq_num) zc_zg_yhsyq_num,b.villagedm from zm_pc_yhlog_dxzgson     a join zm_buildings
b on a.fwbh=b.fwbh left join zm_pc_base_cj c on b.fwbh=c.fwbh where c.fwzt is null and a.fwbh not in( select fwbh  from  zm_pc_base_sqjz where fwzt is not null and yhly='4')   
and a.create_on between '" + startTime + "  00:00:00' and '" + endTime + " 23:59:59'  GROUP BY b.villagedm) a  left join (select sum(zg_ddc_num) jc_zg_ddc_num,sum(zg_yhsyq_num) jc_zg_yhsyq_num,b.villagedm from zm_pc_yhlog_dxzgson_cj     a join zm_buildings b on a.fwbh=b.fwbh  join zm_pc_yhlog_dx_cj c " +
"on a.yhlog_dx_id=c.id join zm_pc_yh_cj d on d.id=c.yhlog_id  where d.jcly='5' and a.create_on between '" + startTime + " 00:00:00' and '" + endTime + " 23:59:59'  GROUP BY b.villagedm)b on a.villagedm=b.villagedm) b2	on b1.villagedm=b2.villagedm  left join (select sum(ccfhwggptzlsl) " +
   "ccfhwggptzlsl,villagedm from zm_pc_gjzz_cjlog where date(create_on)  between '" + startTime + "  00:00:00' and '" + endTime + " 23:59:59' group by villagedm)" +
   " b3 on b1.villagedm=b3.villagedm) b4  left join (SELECT count(DISTINCT CASE WHEN (zm_pc_base.xggdjsbp > 0) THEN zm_pc_base.fwbh ELSE NULL::character varying END) AS xggdjsbpds, sum( CASE WHEN tdsfdb='是' THEN 1 ELSE 0 END) AS ssltdbfjsl,zm_pc_base.xzq,zm_pc_base.xzqdm,zm_pc_base.town,zm_pc_base.village" +
   " FROM (SELECT ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num, t1.*       FROM " + tablename + " t1  JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh  " +
   " WHERE t1.create_on BETWEEN '" + startTime + "  00:00:00' AND '" + endTime + " 23:59:59'   )zm_pc_base WHERE   row_num =1  AND fwbh not in (SELECT DISTINCT fwbh FROM "+table_fwzt+" WHERE final_fwzt IS NOT NULL AND final_create_on = '"+endTime+" 23:59:59) GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq,zm_pc_base.town,zm_pc_base.village) b5 on b4.villagedm=b5.village " +
   "left join zm_department pp on b4.villagedm=pp.id left join zm_department kk on b4.towndm=kk.id order by b4.villagedm ) kk on mm.villagedm=kk.villagedm)" +
   " t where 1=1 " + strWhere + "  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + ""; 


                sql_count = @"select count(1) cnt from (select mm.xzq,mm.village,mm.town,COALESCE(zcount,0) zcount,COALESCE(zg_ddc_num,0) zg_ddc_num,
COALESCE(zg_yhsyq_num,0) zg_yhsyq_num,COALESCE(ccfhwggptzlsl,0) ccfhwggptzlsl,COALESCE(xggdjsbpds,0) xggdjsbpds,COALESCE(ssltdbfjsl,0) ssltdbfjsl,
COALESCE(no_ssltdbfjsl,0) no_ssltdbfjsl,COALESCE(dbl_pencent,'0%')dbl_pencent,COALESCE(azl_pencent,'0%') azl_pencent from zm_building_ledger mm INNER JOIN 
(select b4.*,pp.region_name village ,kk.region_name town,COALESCE(xggdjsbpds,0) xggdjsbpds ,COALESCE(ssltdbfjsl,0) ssltdbfjsl,case when 
(zcount-COALESCE(ssltdbfjsl,0)) <=0 then 0 else (zcount-COALESCE(ssltdbfjsl,0)) end  
         no_ssltdbfjsl,case when ssltdbfjsl= 0 then '0.00%' else concat(  CASE WHEN(round((((ssltdbfjsl)::numeric / (zcount)::numeric) * (100)::numeric), 2) > (100)::numeric)
   THEN 100.00 ELSE round((((ssltdbfjsl)::numeric / (zcount)::numeric) * (100)::numeric), 2)      END, '%') end dbl_pencent,case when xggdjsbpds= 0 then '0.00%' else
     concat( CASE WHEN(round((((xggdjsbpds)::numeric / (zcount)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00 ELSE round((((xggdjsbpds)::numeric / (zcount)::numeric) * (100)::numeric), 2)      END, '%') end azl_pencent 
from (select b1.*,COALESCE(zg_ddc_num,0)zg_ddc_num,COALESCE(zg_yhsyq_num,0) zg_yhsyq_num,
   COALESCE(ccfhwggptzlsl,0)ccfhwggptzlsl from (SELECT count(1) AS zcount,zm_buildings.xzq,zm_buildings.xzqdm,zm_buildings.towndm,zm_buildings.villagedm  FROM (select  t1.xzq,t1.xzqdm,t1.towndm,t1.villagedm ,fwzt,t1.fwbh from zm_buildings t1 LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh::text = t2.fwbh::text) zm_buildings  
where  fwzt is null and fwbh not in( select fwbh  from  zm_pc_base_sqjz where fwzt is not null and yhly='4') GROUP BY zm_buildings.xzqdm, zm_buildings.xzq,zm_buildings.towndm,zm_buildings.villagedm ORDER BY zm_buildings.xzqdm,zm_buildings.towndm,zm_buildings.villagedm) b1  left join( select (COALESCE(zc_zg_ddc_num,0)+COALESCE(jc_zg_ddc_num,0)) zg_ddc_num,
(COALESCE(zc_zg_yhsyq_num,0)+COALESCE(jc_zg_yhsyq_num,0))zg_yhsyq_num,a.villagedm from  
	(select sum(zg_ddc_num) zc_zg_ddc_num,sum(zg_yhsyq_num) zc_zg_yhsyq_num,b.villagedm from zm_pc_yhlog_dxzgson     a join zm_buildings
b on a.fwbh=b.fwbh left join zm_pc_base_cj c on b.fwbh=c.fwbh where c.fwzt is null and a.fwbh not in( select fwbh  from  zm_pc_base_sqjz where fwzt is not null and yhly='4')   
and a.create_on between '" + startTime+"  00:00:00' and '"+ endTime + " 23:59:59'  GROUP BY b.villagedm) a  left join (select sum(zg_ddc_num) jc_zg_ddc_num,sum(zg_yhsyq_num) jc_zg_yhsyq_num,b.villagedm from zm_pc_yhlog_dxzgson_cj     a join zm_buildings b on a.fwbh=b.fwbh  join zm_pc_yhlog_dx_cj c " +
"on a.yhlog_dx_id=c.id join zm_pc_yh_cj d on d.id=c.yhlog_id  where d.jcly='5' and a.create_on between '"+startTime+" 00:00:00' and '"+ endTime + " 23:59:59'  GROUP BY b.villagedm)b on a.villagedm=b.villagedm) b2	on b1.villagedm=b2.villagedm  left join (select sum(ccfhwggptzlsl) " +
    "ccfhwggptzlsl,villagedm from zm_pc_gjzz_cjlog where date(create_on)  between '"+startTime+"  00:00:00' and '"+endTime+" 23:59:59' group by villagedm)" +
    " b3 on b1.villagedm=b3.villagedm) b4  left join (SELECT count(DISTINCT CASE WHEN (zm_pc_base.xggdjsbp > 0) THEN zm_pc_base.fwbh ELSE NULL::character varying END) AS xggdjsbpds, sum( CASE WHEN tdsfdb='是' THEN 1 ELSE 0 END) AS ssltdbfjsl,zm_pc_base.xzq,zm_pc_base.xzqdm,zm_pc_base.town,zm_pc_base.village" +
    " FROM (SELECT ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num, t1.*       FROM "+ tablename + " t1  JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh  " +
    " WHERE t1.create_on BETWEEN '"+startTime+"  00:00:00' AND '"+endTime+ " 23:59:59'   )zm_pc_base WHERE   row_num =1  AND fwbh not in (SELECT DISTINCT fwbh FROM "+table_fwzt+" WHERE final_fwzt IS NOT NULL AND final_create_on = '"+endTime+" 23:59:59) GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq,zm_pc_base.town,zm_pc_base.village) b5 on b4.villagedm=b5.village " +
    "left join zm_department pp on b4.villagedm=pp.id left join zm_department kk on b4.towndm=kk.id order by b4.villagedm ) kk on mm.villagedm=kk.villagedm)" +
    " t where 1=1 " + strWhere;

            }

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
              || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
              || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
         
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }


        /// <summary>
        /// 悬挂警示标牌统计(区县)
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("static/xgjsbp/list")]
        [ProducesResponseType(typeof(RespRet<Page<object>>), 200)]
        public RespRet<Page<object>> getxgjsbplist([FromQuery] int pageindex, [FromQuery] int pagesize,[FromQuery] string  startTime, [FromQuery] string endTime)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            string strWhere = " ";
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            if (model != null)
            {
                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                }
                else if (user.RoleType == 3) //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else  //乡镇
                {
                    strWhere += "and xzqdm='" + model.RegionCode.Substring(0, 6) + "' ";
                }
            }
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string sql = @"select ROW_NUMBER() OVER() index,* from (select b1.*,xggdjsbpds,zcount-xggdjsbpds no_xggdjsbpds, xggdjsbpcount,jzfjs,case when xggdjsbpds= 0 then '0.00%' else concat(
      CASE WHEN(round((((xggdjsbpds)::numeric / (zcount)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00 ELSE round((((xggdjsbpds)::numeric / (zcount)::numeric) * (100)::numeric), 2)  
END, '%') end azl_pencent,case when xggdjsbpcount= 0 then '0.00%' else concat(
      CASE WHEN(round((((xggdjsbpcount)::numeric / (jzfjs)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00 ELSE round((((xggdjsbpcount)::numeric / (jzfjs)::numeric) * (100)::numeric), 2)  
END, '%') end fjzal_pencent from (select xzq,xzqdm,sum(zcount) zcount from pro_hourse_total('"+ endTime + " 23:59:59','"+ table_fwzt + "') group by xzq,xzqdm) b1 left join" +
" (select xzq,xzqdm,sum(CASE WHEN xggdjsbp > 0 THEN 1 ELSE 0 END) AS xggdjsbpds from" +
                        " (SELECT t1.* FROM zm_pc_base t1, zm_buildings t2,zm_pc_yh t3 WHERE t1.fwbh = t2.fwbh  and t1.ID = t3.base_id ) t1 where fwbh not in(select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '"+endTime+" 23:59:59' ))  group by xzqdm,xzq) b2 on b1.xzqdm=b2.xzqdm " +
" left join (select xzq,xzqdm,sum(xggdjsbp) AS xggdjsbpcount,sum(jzfjs) jzfjs from (SELECT t1.* FROM zm_pc_base t1, zm_buildings t2,zm_pc_yh t3 WHERE t1.fwbh = t2.fwbh  and t1.ID = t3.base_id) t1 where fwbh not in(select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '"+endTime+" 23:59:59' )) group by xzqdm,xzq) b3 on b1.xzqdm=b3.xzqdm) t  " +
" where 1=1   " + strWhere + "  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + ""; ;
           
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = @"select count(1) cnt from (select b1.*,xggdjsbpds,zcount-xggdjsbpds no_xggdjsbpds, xggdjsbpcount,jzfjs,case when xggdjsbpds= 0 then '0.00%' else concat(
      CASE WHEN(round((((xggdjsbpds)::numeric / (zcount)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00 ELSE round((((xggdjsbpds)::numeric / (zcount)::numeric) * (100)::numeric), 2)  
END, '%') end azl_pencent,case when xggdjsbpcount= 0 then '0.00%' else concat(
      CASE WHEN(round((((xggdjsbpcount)::numeric / (jzfjs)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00 ELSE round((((xggdjsbpcount)::numeric / (jzfjs)::numeric) * (100)::numeric), 2)  
END, '%') end fjzal_pencent from (select xzq,xzqdm,sum(zcount) zcount from pro_hourse_total('" + endTime + " 23:59:59','" + table_fwzt + "') group by xzq,xzqdm) b1 left join" +
" (select xzq,xzqdm,sum(CASE WHEN xggdjsbp > 0 THEN 1 ELSE 0 END) AS xggdjsbpds from" +
                        " (select t1.* from zm_pc_base t1,zm_buildings t2 where t1.fwbh=t2.fwbh ) t1 where fwbh not in(select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' ))  group by xzqdm,xzq) b2 on b1.xzqdm=b2.xzqdm " +
" left join (select xzq,xzqdm,sum(xggdjsbp) AS xggdjsbpcount,sum(jzfjs) jzfjs from (select t1.* from zm_pc_base t1,zm_buildings t2 where t1.fwbh=t2.fwbh) t1 where fwbh not in(select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + endTime + " 23:59:59' )) group by xzqdm,xzq) b3 on b1.xzqdm=b3.xzqdm) t  " +
" where 1=1   " + strWhere;

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }
        /// <summary>
        /// 获取当前行政区下的未安装固定警示标牌的房屋
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("static/xgjsbp/getNoxgjsbplist")]
        public RespRet<Page<object>> getNoxgjsbplist([FromQuery] int pageIndex, [FromQuery] int pageSize, [FromQuery] string startTime, [FromQuery] string endTime,  [FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string txtKeyWord)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);
            string strWhere = " ";
            if (model != null)
            {
                if (user.RoleType == 3) //区级
                {
                    //strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
            }
         

            string where = "";
            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and t3.xzqdm='" + xzqdm + "' and t3.towndm='" + towndm + "' and t3.villagedm='" + villagedm + "'";
                where = " and xzqdm='" + xzqdm + "' and (towndm='" + towndm + "') and  (villagedm='" + villagedm + "')";
            }
            else if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and t3.xzqdm='" + xzqdm + "' and t3.towndm='" + towndm + "' ";
                where = " and xzqdm='" + xzqdm + "' and (towndm='" + towndm + "') ";
            }
            else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and t3.xzqdm='" + xzqdm + "' ";
                where = " and xzqdm='" + xzqdm + "' ";
            }
            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                where += " and (hzxm like '%" + txtKeyWord + "%' OR address like '%" + txtKeyWord + "%' OR fwbh like '%" + txtKeyWord + "%') ";
            }
            string toDay = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(toDay);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string sql = "";
            string today = DateTime.Now.ToString("yyyy-MM-dd");
           
                sql = @"select ROW_NUMBER() OVER ()index ,* from (select xzq,xzqdm,town,towndm,village,villagedm,hzxm,address,fwbh,xggdjsbp  from "
        + " (select t1.* from ( SELECT t3.* FROM(SELECT   t1.ID,t1.xzq, t1.xzqdm,t1.towndm town,t1.town towndm,t1.villagedm village,t1.village villagedm,hzxm,address,fwbh, "
        + " cs,create_on,xggdjsbp FROM zm_pc_base t1      ) t3  INNER JOIN zm_pc_yh m2 ON t3.ID = m2.base_id " 
        + " WHERE  xggdjsbp=0    "+ strWhere + "    and t3.fwbh in (select fwbh from zm_buildings) AND t3.fwbh not in ( select DISTINCT fwbh from " + table_fwzt + " "
        + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '"+ toDay + " 23:59:59' ) ) ) t1 ORDER BY villagedm )  t  where 1=1   "+ where
        + " union all"
        + " select xzq,xzqdm,town,towndm,village,villagedm,hzxm,address,fwbh,'0' xggdjsbp from zm_buildings where fwbh not in( select DISTINCT fwbh from "+ table_fwzt + " where  "
        + " (final_fwzt IS NOT NULL) 	and (final_create_on = '"+ toDay + " 23:59:59' ) ) and fwbh not in(select fwbh  from (select t1.* from ( SELECT t3.* FROM(SELECT   t1.ID,t1.xzq, "
        + " t1.xzqdm,t1.towndm town,t1.town towndm,t1.villagedm village,t1.village villagedm,hzxm,address,fwbh, cs,create_on,xggdjsbp FROM zm_pc_base t1    ) t3  INNER JOIN zm_pc_yh m2 ON t3.ID = m2.base_id  WHERE 1=1  "+ strWhere 
        + " AND t3.fwbh not in ( select DISTINCT fwbh from "+table_fwzt+" where  (final_fwzt IS NOT NULL) 	and (final_create_on = '"+ endTime + " 23:59:59' ) )  ) t1 )  t  "
        + " where 1=1 "+where+"  ORDER BY villagedm ) "+where+") t where 1=1 " + where + " limit " + pageSize + " offset(" + (pageIndex - 1) + ") * " + pageSize;
            
           
            string sql_count = "";
            
                sql_count = @"select count(1) cnt from (select xzq,xzqdm,town,towndm,village,villagedm,hzxm,address,fwbh,xggdjsbp  from "
        + " (select t1.* from ( SELECT t3.* FROM(SELECT   t1.ID,t1.xzq, t1.xzqdm,t1.towndm town,t1.town towndm,t1.villagedm village,t1.village villagedm,hzxm,address,fwbh, "
        + " cs,create_on,xggdjsbp FROM zm_pc_base t1      ) t3  INNER JOIN zm_pc_yh m2 ON t3.ID = m2.base_id "
        + " WHERE  xggdjsbp=0    " + strWhere + "    and t3.fwbh in (select fwbh from zm_buildings)  AND t3.fwbh not in ( select DISTINCT fwbh from " + table_fwzt + " "
        + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + toDay + " 23:59:59' ) ) ) t1 ORDER BY villagedm )  t  where 1=1   " + where
        + " union all"
        + " select xzq,xzqdm,town,towndm,village,villagedm,hzxm,address,fwbh,'0' xggdjsbp from zm_buildings where fwbh not in( select DISTINCT fwbh from " + table_fwzt + " where  "
        + " (final_fwzt IS NOT NULL) 	and (final_create_on = '" + toDay + " 23:59:59' ) ) and fwbh not in(select fwbh  from (select t1.* from ( SELECT t3.* FROM(SELECT   t1.ID,t1.xzq, "
        + " t1.xzqdm,t1.towndm town,t1.town towndm,t1.villagedm village,t1.village villagedm,hzxm,address,fwbh, cs,create_on,xggdjsbp FROM zm_pc_base t1    ) t3  INNER JOIN zm_pc_yh m2 ON t3.ID = m2.base_id  WHERE 1=1  " + strWhere
        + " AND t3.fwbh not in ( select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' ) )  ) t1 )  t  "
        + " where 1=1 " + where + "  ORDER BY villagedm ) " + where + ") t where 1=1 " + where;
            

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
      || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
      || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }

        /// <summary>
        /// 充电方式统计(区县)
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("static/cdfs/list")]
        [ProducesResponseType(typeof(RespRet<Page<object>>), 200)]
        public RespRet<Page<object>> getcdfslist([FromQuery] int pageindex, [FromQuery] int pagesize)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            string strWhere = " ";
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            if (model != null)
            {
                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                }
                else if (user.RoleType == 3) //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else  //乡镇
                {
                    strWhere += "and xzqdm='" + model.RegionCode.Substring(0, 6) + "' ";
                }
            }
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";

            DateTime endData = Convert.ToDateTime(today);
            table_fwzt = "zm_building_fwzt_" + endData.Year;

            string sql = @"select ROW_NUMBER() OVER() index,* from (select xzq,xzqdm,
					 sum(CASE WHEN name = '单插口' THEN COALESCE(count,0) else 0 END) AS dchak,
					 sum(CASE WHEN name = '集中充电桩' THEN COALESCE(count,0) else 0  END) AS jzcdz,
					 sum(CASE WHEN name = '单仓口' THEN COALESCE(count,0) else 0  END) AS dcangk,
					 sum(CASE WHEN name = '充(换)电柜'  THEN COALESCE(count,0) else 0  END) AS cdg,
					 sum(CASE WHEN name = '其他'  THEN COALESCE(count,0) else 0  END) AS qt
					from (SELECT split_part(cdfs, '|', 1) AS name,COUNT(*) AS count,xzq,xzqdm FROM  zm_pc_base where fwbh not in(select distinct fwbh from "+ table_fwzt + " where final_fwzt is not null and   final_create_on='"+today+ " 23:59:59') GROUP BY NAME,xzq,xzqdm) T  group by xzq,xzqdm order by xzqdm) t where 1=1 " + strWhere + "  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + ""; ;

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = @"select count(1) cnt from (select xzq,xzqdm,
					 sum(CASE WHEN name = '单插口' THEN COALESCE(count,0) else 0 END) AS dchak,
					 sum(CASE WHEN name = '集中充电桩' THEN COALESCE(count,0) else 0  END) AS jzcdz,
					 sum(CASE WHEN name = '单仓口' THEN COALESCE(count,0) else 0  END) AS dcangk,
					 sum(CASE WHEN name = '充(换)电柜'  THEN COALESCE(count,0) else 0  END) AS cdg,
					 sum(CASE WHEN name = '其他'  THEN COALESCE(count,0) else 0  END) AS qt
					from (SELECT split_part(cdfs, '|', 1) AS name,COUNT(*) AS count,xzq,xzqdm FROM  zm_pc_base where fwbh not in(select distinct fwbh from "+ table_fwzt + " where final_fwzt is not null and   final_create_on='"+today+ " 23:59:59') GROUP BY NAME,xzq,xzqdm) T  group by xzq,xzqdm order by xzqdm) t where 1=1" + strWhere;

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }

        /// <summary>
        /// 消防设施统计(区县)
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("static/xfss/list")]
        [ProducesResponseType(typeof(RespRet<Page<object>>), 200)]
        public RespRet<Page<object>> getxfsslist([FromQuery] int pageindex, [FromQuery] int pagesize)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            string strWhere = " ";
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            if (model != null)
            {
                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                }
                else if (user.RoleType == 3) //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else  //乡镇
                {
                    strWhere += "and xzqdm='" + model.RegionCode.Substring(0, 6) + "' ";
                }
            }

            string sql = @"select ROW_NUMBER() OVER() index,* from (select xzq,xzqdm, sum(sfszszxhs) sfszszxhs,sum(xfctdsl)xfctdsl,sum(abzjsxfscsl) abzjsxfscsl,sum(pzxfsb) pzxfsb,sum(jsswxhs) jsswxhs,sum(sfjszzxfdhxxxfz)sfjszzxfdhxxxfz,sum(sfjswxxfz)sfjswxxfz,sum(xfctdslxz)xfctdslxz from  zm_pc_gjzz_cj group by xzq,xzqdm order by xzqdm) t where 1=1  " + strWhere + "  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + ""; ;

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = @"select count(1) cnt from (select xzq,xzqdm, sum(sfszszxhs) sfszszxhs,sum(xfctdsl)xfctdsl,sum(abzjsxfscsl) abzjsxfscsl,sum(pzxfsb) pzxfsb,sum(jsswxhs) jsswxhs,sum(sfjszzxfdhxxxfz)sfjszzxfdhxxxfz,sum(sfjswxxfz)sfjswxxfz,sum(xfctdslxz)xfctdslxz from  zm_pc_gjzz_cj group by xzq,xzqdm order by xzqdm) t where 1=1  " + strWhere;


            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }


        /// <summary>
        /// 充电设施配比情况(区县)
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("static/cdsspbqk/list")]
        [ProducesResponseType(typeof(RespRet<Page<object>>), 200)]
        public RespRet<Page<object>> getcdsspbqklist([FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string startTime, [FromQuery] string endTime)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            string strWhere = " ";
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            if (model != null)
            {
                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                }
                else if (user.RoleType == 3) //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else  //乡镇
                {
                    strWhere += "and xzqdm='" + model.RegionCode.Substring(0, 6) + "' ";
                }
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = "2024-01-01";
                endTime = DateTime.Now.ToString("yyyy-MM-dd");
            }
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string sql = @"  select ROW_NUMBER() OVER() index,*  from (select t1.*,bdfwjzrydddzxcsl,czryrs,COALESCE(cdjksl,0) cdjksl,COALESCE(sqfwfsl,0) sqfwfsl,COALESCE(xy,0) xy,COALESCE(xd,0) xd,COALESCE(dy,0) dy from (SELECT DISTINCT zm_buildings.xzqdm,
                    zm_buildings.xzq 
                   FROM zm_buildings) t1 left join				 			 			 
                (select COALESCE(sum(qcxycdjk),0)+COALESCE(sum(tzzfybjcdssjk),0)  cdjksl,
                count(case when sfsqfwftcf=1 then 1 end) sqfwfsl,xzqdm,
                count(case when cddf=3 then 1 end)+count(case when bjcdssdfqk=3 then 1 end) xy,
                count(case when cddf=2 then 1 end)+count(case when bjcdssdfqk=2 then 1 end) xd,
                count(case when cddf=1 then 1 end)+count(case when bjcdssdfqk=1 then 1 end) dy
                from zm_pc_gjzz_cj WHERE   create_on between '" + startTime + " 00:00:00'and '" + endTime + " 23:59:59'   GROUP BY xzqdm) t2 on t1.xzqdm=t2.xzqdm     left join  (select  sum(bdfwjzrydddzxcsl) AS bdfwjzrydddzxcsl,sum(czryrs) czryrs,xzq, xzqdm     from (select m1.*   from (select t1.* from zm_pc_base t1,zm_buildings t2 where t1.fwbh=t2.fwbh ) m1 ) t  where fwbh not in (select fwbh from " + table_fwzt + " where final_create_on='" + endTime + " 23:59:59'   and final_fwzt is not null)  GROUP BY xzqdm, xzq) t3 on t1.xzqdm=t3.xzqdm ORDER BY t3.xzqdm) t where 1=1"
    + strWhere + "  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = @"select count(1) cnt  from (select t1.*,bdfwjzrydddzxcsl,czryrs,COALESCE(cdjksl,0) cdjksl,COALESCE(sqfwfsl,0) sqfwfsl,COALESCE(xy,0) xy,COALESCE(xd,0) xd,COALESCE(dy,0) dy from (SELECT DISTINCT zm_buildings.xzqdm,
                    zm_buildings.xzq 
                   FROM zm_buildings) t1 left join				 			 			 
                (select COALESCE(sum(qcxycdjk),0)+COALESCE(sum(tzzfybjcdssjk),0)  cdjksl,
                count(case when sfsqfwftcf=1 then 1 end) sqfwfsl,xzqdm,
                count(case when cddf=3 then 1 end)+count(case when bjcdssdfqk=3 then 1 end) xy,
                count(case when cddf=2 then 1 end)+count(case when bjcdssdfqk=2 then 1 end) xd,
                count(case when cddf=1 then 1 end)+count(case when bjcdssdfqk=1 then 1 end) dy
                from zm_pc_gjzz_cj WHERE   create_on between '" + startTime + " 00:00:00'and '" + endTime + " 23:59:59'   GROUP BY xzqdm) t2 on t1.xzqdm=t2.xzqdm     left join  (select  sum(bdfwjzrydddzxcsl) AS bdfwjzrydddzxcsl,sum(czryrs) czryrs,xzq, xzqdm     from (select m1.*   from (select t1.* from zm_pc_base t1,zm_buildings t2 where t1.fwbh=t2.fwbh ) m1 ) t  where fwbh not in (select fwbh from " + table_fwzt + " where final_create_on='" + endTime + " 23:59:59'   and final_fwzt is not null)  GROUP BY xzqdm, xzq) t3 on t1.xzqdm=t3.xzqdm ORDER BY t3.xzqdm) t where 1=1 "
          + strWhere;

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }



        /// <summary>
        /// 拆除防护网情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("static/ccfhwqk/list")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> getccfhwqklist([FromQuery] string startTime, [FromQuery] string endTime)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<object>(false, "user not logined");
            }

            string strWhere = " ";
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<object>(false, "用户名不存在");
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = "2024-01-01";
                endTime = DateTime.Now.ToString("yyyy-MM-dd");
            }
            string sql = @"select ROW_NUMBER() OVER() index,* from (select ckfhwggptzlsl zcount,case when (COALESCE(ckfhwggptzlsl,0) -COALESCE(ccfhwggptzlsl,0)) <0 then 0 else (COALESCE(ckfhwggptzlsl,0) -COALESCE(ccfhwggptzlsl,0)) end sy,
case when COALESCE(ccfhwggptzlsl,0)>COALESCE(ckfhwggptzlsl,0) then COALESCE(ckfhwggptzlsl,0) else COALESCE(ccfhwggptzlsl,0) end  ccfhwggptzlsl ,
name , code,  CASE WHEN ccl > 100 THEN 100.00  when ccl is null then 0.00 ELSE ccl END ccl, concat(  CASE WHEN ccl > 100 THEN 100.00  when ccl is null then 0.00 ELSE ccl END ,'%') ccl_pencent from (	select  zcount,code,name,case when ckfhwggptzlsl is null then 0 else ckfhwggptzlsl end ckfhwggptzlsl,case when ccfhwggptzlsl is null then 0 else ccfhwggptzlsl end ccfhwggptzlsl  ,
			case when 	round(ccfhwggptzlsl*1.0*100/ckfhwggptzlsl,2) is null then 0.00 else round(ccfhwggptzlsl*1.0*100/ckfhwggptzlsl,2) end  
ccl  from (select sum(zcount)zcount,xzqdm code,max(xzq) name, case when sum(ckfhwggptzlsl)=0 then null else  sum(ckfhwggptzlsl) end ckfhwggptzlsl 
from  jbxxgqview GROUP BY xzqdm) a left join (select xzqdm,sum(ccfhwggptzlsl) ccfhwggptzlsl from zm_pc_gjzz_cjlog where create_on 
between  '"+startTime+" 00:00:00' and '"+endTime+" 23:59:59'  GROUP BY xzqdm)  b	 on a.code=b.xzqdm)a ORDER BY ccl desc,code  ) t where 1=1 ";

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<object>(false, "不符合规范，禁止执行");
            }
            string sql_count = @"select count(1) cnt from (select ckfhwggptzlsl zcount,case when (COALESCE(ckfhwggptzlsl,0) -COALESCE(ccfhwggptzlsl,0)) <0 then 0 else (COALESCE(ckfhwggptzlsl,0) -COALESCE(ccfhwggptzlsl,0)) end sy,
case when COALESCE(ccfhwggptzlsl,0)>COALESCE(ckfhwggptzlsl,0) then COALESCE(ckfhwggptzlsl,0) else COALESCE(ccfhwggptzlsl,0) end  ccfhwggptzlsl ,
name , code,  CASE WHEN ccl > 100 THEN 100.00  when ccl is null then 0.00 ELSE ccl END ccl, concat(  CASE WHEN ccl > 100 THEN 100.00  when ccl is null then 0.00 ELSE ccl END ,'%') ccl_pencent from (	select  zcount,code,name,case when ckfhwggptzlsl is null then 0 else ckfhwggptzlsl end ckfhwggptzlsl,case when ccfhwggptzlsl is null then 0 else ccfhwggptzlsl end ccfhwggptzlsl  ,
			case when 	round(ccfhwggptzlsl*1.0*100/ckfhwggptzlsl,2) is null then 0.00 else round(ccfhwggptzlsl*1.0*100/ckfhwggptzlsl,2) end  
ccl  from (select sum(zcount)zcount,xzqdm code,max(xzq) name, case when sum(ckfhwggptzlsl)=0 then null else  sum(ckfhwggptzlsl) end ckfhwggptzlsl 
from  jbxxgqview GROUP BY xzqdm) a left join (select xzqdm,sum(ccfhwggptzlsl) ccfhwggptzlsl from zm_pc_gjzz_cjlog where create_on 
between  '" + startTime + " 00:00:00' and '" + endTime + " 23:59:59'  GROUP BY xzqdm)  b	 on a.code=b.xzqdm)a ORDER BY ccl desc,code  ) t where 1=1 ";
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<object>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<object>(true, "ok",  datas);

        }

        /// <summary>
        /// 统计满足两条疏散通道情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("static/sstdbqk/list")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> getsstdbqklist([FromQuery] string startTime, [FromQuery] string endTime)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<object>(false, "user not logined");
            }

            string strWhere = " ";
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<object>(false, "用户名不存在");
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = "2024-01-01";
                endTime = DateTime.Now.ToString("yyyy-MM-dd");
            }
            string sql = @"	select ROW_NUMBER() OVER() index,* from (SELECT  t1.xzq, t1.xzqdm,COALESCE((t1.zcount)::numeric, (0)::numeric) AS zcount,COALESCE(t2.ssltdbfjsl, (0)::bigint) AS ssltdbfjsl,case when (COALESCE(zcount,0) -COALESCE(ssltdbfjsl,0)) <0 then 0 else (COALESCE(zcount,0) -COALESCE(ssltdbfjsl,0)) end no_ssltdbfjsl,concat(  CASE  WHEN (round((((ssltdbfjsl)::numeric / (zcount)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00   ELSE round((((ssltdbfjsl)::numeric / (zcount)::numeric) * (100)::numeric), 2) END,'%') dbl_pencent
FROM (SELECT     count(1) AS zcount, zm_buildings.xzq,zm_buildings.xzqdm 
        FROM (SELECT t1.*, fwzt,hcfwzt FROM zm_buildings t1 LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh::text = t2.fwbh::text  LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh) AS zm_buildings  
        WHERE fwzt IS NULL and hcfwzt is null GROUP BY zm_buildings.xzqdm, zm_buildings.xzq ORDER BY zm_buildings.xzqdm) AS t1
    LEFT JOIN (
        SELECT  sum( CASE WHEN tdsfdb='是' THEN 1 else 0 END) AS ssltdbfjsl,zm_pc_base.xzq,zm_pc_base.xzqdm FROM (
            SELECT ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num, t1.*  FROM zm_pc_baselog t1  JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh   WHERE t1.create_on BETWEEN '" + startTime+" 00:00:00' AND '"+endTime+" 23:59:59'  and sfyzc='是'     "                                                         
      + "   ) AS zm_pc_base where row_num=1  AND fwbh not in(select * from zm_pc_base_cj where fwzt is not null) and fwbh not in (SELECT fwbh FROM zm_pc_base_sqjz WHERE yhly = '4' and fwzt is not null)  GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq) AS t2 ON t1.xzqdm::text = t2.xzqdm::text) t where 1=1  ";

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<object>(false, "不符合规范，禁止执行");
            }
            string sql_count = @"	select count(1) cnt from (SELECT  t1.xzq, t1.xzqdm,COALESCE((t1.zcount)::numeric, (0)::numeric) AS zcount,COALESCE(t2.ssltdbfjsl, (0)::bigint) AS ssltdbfjsl,case when (COALESCE(zcount,0) -COALESCE(ssltdbfjsl,0)) <0 then 0 else (COALESCE(zcount,0) -COALESCE(ssltdbfjsl,0)) end no_ssltdbfjsl,concat(  CASE  WHEN (round((((ssltdbfjsl)::numeric / (zcount)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00   ELSE round((((ssltdbfjsl)::numeric / (zcount)::numeric) * (100)::numeric), 2) END,'%') dbl_pencent
FROM (SELECT     count(1) AS zcount, zm_buildings.xzq,zm_buildings.xzqdm 
        FROM (SELECT t1.*, fwzt,hcfwzt FROM zm_buildings t1 LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh::text = t2.fwbh::text LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh) AS zm_buildings  
        WHERE fwzt IS NULL and hcfwzt is null GROUP BY zm_buildings.xzqdm, zm_buildings.xzq ORDER BY zm_buildings.xzqdm) AS t1
    LEFT JOIN (
        SELECT  sum( CASE WHEN tdsfdb='是' THEN 1 else 0 END) AS ssltdbfjsl,zm_pc_base.xzq,zm_pc_base.xzqdm FROM (
            SELECT ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num, t1.*  FROM zm_pc_baselog t1  JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh  
    WHERE t1.create_on BETWEEN '" + startTime + " 00:00:00' AND '"+endTime+" 23:59:59'    and sfyzc='是'    "                                                          
      + "   ) AS zm_pc_base where row_num=1  AND fwbh not in(select * from zm_pc_base_cj where fwzt is not null) and fwbh not in (SELECT fwbh FROM zm_pc_base_sqjz WHERE yhly = '4' and fwzt is not null)    GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq) AS t2 ON t1.xzqdm::text = t2.xzqdm::text) t where 1=1  ";
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<object>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<object>(true, "ok", datas);

        }


        /// <summary>
        /// 统计现有基础数据
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("static/baseinfo/detail")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> getbaseinfodetail()
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<object>(false, "user not logined");
            }

            string strWhere = " ";
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<object>(false, "用户名不存在");
            }
           
            string sql = @"	 select count(DISTINCT xzqdm) xzq_count,count(DISTINCT towndm) town_count,count(DISTINCT villagedm) village_count,
  (select  count(1) zcount from (select t1.*,fwzt,hcfwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh   ) t  where fwzt is null and hcfwzt is null) zcount,
	 (select  count(1) onecount from (select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_base t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh  LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh  ) t  where jcfwzt is null and hcfwzt is null and cs<=1) onecount,
	 (select  count(1) twocount from (select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_base t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh   LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh ) t  where jcfwzt is null and hcfwzt is null and cs=2) twocount,
	 (select  count(1) threecount from (select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_base t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh    LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh ) t  where fwzt is null and hcfwzt is null and cs>=3) threecount,
 (select sum(CASE WHEN name = 'KTV' THEN COALESCE(count,0) else 0 END) AS ""KTV"" from (SELECT split_part(cjzwqtjyytmsg, '|', 1) AS name,COUNT(*) AS count FROM  (select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_base t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh   
LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh) m1 where jcfwzt is null and hcfwzt is null t1 GROUP BY name) t) ktvcount,
 (select sum(CASE WHEN name = '餐饮' THEN COALESCE(count,0) else 0 END) AS ""KTV"" from (SELECT split_part(cjzwqtjyytmsg, '|', 1) AS name,COUNT(*) AS count FROM  (select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_base t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh   
LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh) m1 where jcfwzt is null and hcfwzt is null t1 GROUP BY name) t) cycount,
 (select sum(CASE WHEN name = '茶社' THEN COALESCE(count,0) else 0 END) AS ""KTV"" from (SELECT split_part(cjzwqtjyytmsg, '|', 1) AS name,COUNT(*) AS count FROM  (select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_base t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh   
LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh) m1 where jcfwzt is null and hcfwzt is null t1  GROUP BY name) t) cscount,
 (select sum(CASE WHEN name = '加工生产' THEN COALESCE(count,0) else 0 END) AS ""KTV"" from (SELECT split_part(cjzwqtjyytmsg, '|', 1) AS name,COUNT(*) AS count FROM  (select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_base t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh   
LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh) m1 where jcfwzt is null and hcfwzt is null t1  GROUP BY name) t) jgsccount,
 (select sum(CASE WHEN name = '教学培训' THEN COALESCE(count,0) else 0 END) AS ""KTV"" from (SELECT split_part(cjzwqtjyytmsg, '|', 1) AS name,COUNT(*) AS count FROM  (select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_base t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh   
LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh) m1 where jcfwzt is null and hcfwzt is null t1  GROUP BY name) t) jxpxcount,
 (select sum(CASE WHEN name = '美甲美睫' THEN COALESCE(count,0) else 0 END) AS ""KTV"" from (SELECT split_part(cjzwqtjyytmsg, '|', 1) AS name,COUNT(*) AS count FROM  (select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_base t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh   
LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh) m1 where jcfwzt is null and hcfwzt is null t1  GROUP BY name) t) mjmjcount,
 (select sum(CASE WHEN name = '美容美发' THEN COALESCE(count,0) else 0 END) AS ""KTV"" from (SELECT split_part(cjzwqtjyytmsg, '|', 1) AS name,COUNT(*) AS count FROM  (select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_base t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh   
LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh) m1 where jcfwzt is null and hcfwzt is null t1  GROUP BY name) t) mrmfcount,
 (select sum(CASE WHEN name = '棋牌室' THEN COALESCE(count,0) else 0 END) AS ""KTV"" from (SELECT split_part(cjzwqtjyytmsg, '|', 1) AS name,COUNT(*) AS count FROM  (select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_base t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh   
LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh) m1 where jcfwzt is null and hcfwzt is null t1 GROUP BY name) t) qpscount,
 (select sum(CASE WHEN name = '商店' THEN COALESCE(count,0) else 0 END) AS ""KTV"" from (SELECT split_part(cjzwqtjyytmsg, '|', 1) AS name,COUNT(*) AS count FROM  (select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_base t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh   
LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh) m1 where jcfwzt is null and hcfwzt is null t1 GROUP BY name) t) sdcount,
 (select sum(CASE WHEN name = '网吧' THEN COALESCE(count,0) else 0 END) AS ""KTV"" from (SELECT split_part(cjzwqtjyytmsg, '|', 1) AS name,COUNT(*) AS count FROM  (select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_base t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh   
LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh) m1 where jcfwzt is null and hcfwzt is null t1 GROUP BY name) t) wbcount,
 (select sum(CASE WHEN name = '医疗诊所' THEN COALESCE(count,0) else 0 END) AS ""KTV"" from (SELECT split_part(cjzwqtjyytmsg, '|', 1) AS name,COUNT(*) AS count FROM  (select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_base t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh   
LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh) m1 where jcfwzt is null and hcfwzt is null t1 GROUP BY name) t) ylzscount,
 (select sum(CASE WHEN name = '足疗、按摩' THEN COALESCE(count,0) else 0 END) AS ""KTV"" from (SELECT split_part(cjzwqtjyytmsg, '|', 1) AS name,COUNT(*) AS count FROM  (select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_base t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh   
LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh) m1 where jcfwzt is null and hcfwzt is null t1 GROUP BY name) t) zlamcount,
 (select sum(CASE WHEN name = '其他' THEN COALESCE(count,0) else 0 END) AS ""KTV"" from (SELECT split_part(cjzwqtjyytmsg, '|', 1) AS name,COUNT(*) AS count FROM  (select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_base t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh   
LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh) m1 where jcfwzt is null and hcfwzt is null GROUP BY name) t) qtcount
	 from zm_building_ledger
 
   ";

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<object>(false, "不符合规范，禁止执行");
            }


            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<object>(true, "ok", datas);

        }


        /// <summary>
        /// 统计房屋基本基础数据
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("static/hourseInfo/detail")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> getHourseInfodetail()
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<object>(false, "user not logined");
            }

            string strWhere = " ";
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<object>(false, "用户名不存在");
            }

            string sql = @"	select sum(jzrks)  jzrks,sum(zzrs) zzrs,sum(jzfjs) jzfjs,sum(czryrs) czryrs,sum(bdfwjzrydddzxcsl) bdfwjzrydddzxcsl,
                        (select  sum(qcxycdjk) + sum(tzzfybjcdssjk) from zm_pc_gjzz_cj ) xycdjkcount
                         from ( select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_base t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh)t where jcfwzt is null and hcfwzt is null ";

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<object>(false, "不符合规范，禁止执行");
            }
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<object>(true, "ok", datas);

        }

        /// <summary>
        /// 统计自查和检查情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("static/checkhourseInfo/detail")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> getCheckhourseInfodetail()
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<object>(false, "user not logined");
            }

            string strWhere = " ";
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<object>(false, "用户名不存在");
            }
            DateTime today = DateTime.Now; // 获取当前日期和时间
            DateTime sevenDaysAgo = today.AddDays(-7); // 获取7天前的日期

            Console.WriteLine("今天的日期是: " + today.ToShortDateString());
            Console.WriteLine("7天前的日期是: " + sevenDaysAgo.ToShortDateString());
            string startTime = sevenDaysAgo.ToString("yyyy-MM-dd");
            string endTime = today.ToString("yyyy-MM-dd");


            DateTime firstDayOfMonth = new DateTime(today.Year, today.Month, 1);
            DateTime lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1);

            Console.WriteLine("今天的日期是: " + today.ToShortDateString());
            Console.WriteLine("7天前的日期是: " + sevenDaysAgo.ToShortDateString());
            string m_startTime = firstDayOfMonth.ToString("yyyy-MM-dd");
            string m_endTime = lastDayOfMonth.ToString("yyyy-MM-dd");

            string sql = @"			select * from 	"+
" (select (round(zccount*1.00*100/zcount,2)) zcl from (select count(1) zcount,(select count(1) zccount from (select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_yh t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh    LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh) m1 where jcfwzt is null and hcfwzt is null and date(create_on) BETWEEN '" + startTime + " 00:00:00' and '"+endTime+ " 23:59:59') from zm_buildings) tt)as t1," +
" (select (round(zccount*1.00*100/zcount,2)) jcl from (select count(1) zcount,(select count(1) zccount from (select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_yh_cj t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh    LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh) m1 where jcfwzt is null and hcfwzt is null date(create_on) BETWEEN '" + m_startTime+" 00:00:00' and '"+m_endTime+" 23:59:59') from zm_buildings)tt2) as t2,"+
" (select zccount as  bzcount from (select count(1) zcount,(select count(1) zccount from (select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_yh t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh    LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh) m1 where jcfwzt is null and hcfwzt is null date(create_on) BETWEEN '" + startTime+" 00:00:00' and '"+endTime+" 23:59:59') from zm_buildings)tt3) as t3,"+
" (select zccount as  monthcount from (select count(1) zcount,(select count(1) zccount from (select t1.*,t2.fwzt jcfwzt,hcfwzt from zm_pc_yh t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh    LEFT JOIN (SELECT fwbh, fwzt hcfwzt FROM zm_pc_base_sqjz WHERE yhly = '4') AS t3 ON t1.fwbh = t3.fwbh) m1 where jcfwzt is null and hcfwzt is null date(create_on) BETWEEN '" + m_startTime + " 00:00:00' and '"+m_endTime+" 23:59:59') from zm_buildings)tt5) as t5,"+
" (select COALESCE(zcyhnum,0)+ COALESCE(jcyhnum,0) nowyhnum from (select count(1) zcyhnum,(select count(1) jcyhnum from zm_pc_yhlog_dx_cj where zg_value=0)  from zm_pc_yhlog_dx where zg_value=0)tt5"+
" ) t6 ";

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<object>(false, "不符合规范，禁止执行");
            }
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<object>(true, "ok", datas);

        }

        /// <summary>
        /// 统计自查和检查情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("static/ddzxc/list")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> getddzxcList([FromQuery] string startTime, [FromQuery] string endTime)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<object>(false, "user not logined");
            }

            string strWhere = " ";
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<object>(false, "用户名不存在");
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = "2024-01-01";
                endTime = DateTime.Now.ToString("yyyy-MM-dd");
            }
            string sql = @"		select ROW_NUMBER() OVER() index,* from (		select    bdfwjzrydddzxcsl, COALESCE(zg,0)qlddcsl,t1.xzq,t1.xzqdm  from  (select a.xzq xzq,"
  + "     a.use_electric_bicycle+b.use_electric_bicycle fx,a.xzqdm from  zcyhgqtext('"+startTime+" 00:00:00','"+endTime+" 23:59:59') a,"
  +"         jcyhgqtext('"+ startTime + " 00:00:00','"+endTime+ " 23:59:59') b where a.xzqdm=b.xzqdm)t1 join (select  (case when zc is null then 0 else zc "
  + "     end+ case when jc is null then 0 else jc end) zg,"
  + "     a.xzqdm from  (select sum(zg_ddc_num) zc,b.xzqdm from zm_pc_yhlog_dxzgson a join zm_buildings b on a.fwbh=b.fwbh left join zm_pc_base_cj c "
  + "     on b.fwbh=c.fwbh where fwzt is null and a.create_on<(case when c.fwzt is not null and yhly='5' then c.create_on else now() end) and"
  + "  a.create_on between '"+startTime+" 00:00:00' and '"+endTime+ " 23:59:59'  GROUP BY b.xzqdm) a full join (select sum(zg_ddc_num) jc,b.xzqdm from "
  + "   zm_pc_yhlog_dxzgson_cj a join zm_buildings b on a.fwbh=b.fwbh  join zm_pc_yhlog_dx_cj c on a.yhlog_dx_id=c.id join zm_pc_yh_cj d on d.id=c.yhlog_id "
  + "where d.jcly='5' and a.create_on between '"+startTime+" 00:00:00' and '"+endTime+ " 23:59:59'  GROUP BY b.xzqdm)b on a.xzqdm=b.xzqdm) t2 on "
  + "  t1.xzqdm=t2.xzqdm  RIGHT JOIN  ( SELECT "
  + "         sum(zm_pc_base.bdfwjzrydddzxcsl) AS bdfwjzrydddzxcsl,"
  + "         zm_pc_base.xzq,"
  + "         zm_pc_base.xzqdm"
  + "        FROM (SELECT ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num, t1.*  FROM zm_pc_baselog t1  "
  + " JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh   WHERE t1.create_on BETWEEN '"+startTime+" 00:00:00' and '"+endTime+ " 23:59:59'             "                                                    
  + "      )zm_pc_base"
  + "       WHERE  fwzt is null  and sfyzc='是'" 
  + "       GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq)  t3 on t1.xzqdm=t3.xzqdm order by qlddcsl desc) t where 1=1 "; 

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<object>(false, "不符合规范，禁止执行");
            }
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<object>(true, "ok", datas);

        }


        /// <summary>
        /// 统计人员密集场所
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("static/rymjcs/list")]
        [Produces("application/json")]
        public RespRet<Page<object>> rymjcsList([FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string startTime, [FromQuery] string endTime)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "";

            if (user.RoleType == 4 || user.RoleType == 3)
            {
                strWhere += "and xzqdm ='" + model.RegionCode + "'";
            }

            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";

            DateTime endData = Convert.ToDateTime(today);
            table_fwzt = "zm_building_fwzt_" + endData.Year;


            string sql = @"select ROW_NUMBER() OVER() AS idex,* from (select n1.xzq,n1.xzqdm,  sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%餐饮%'::text) THEN 1 ELSE 0 END) AS cy_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%商店%'::text) THEN 1 ELSE 0 END) AS sd_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%美容美发%'::text) THEN 1 ELSE 0 END) AS mrmf_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%KTV%'::text) THEN 1 ELSE 0 END) AS ktv_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%网吧%'::text) THEN 1 ELSE 0 END) AS wb_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%美甲美睫%'::text) THEN 1 ELSE 0 END) AS mjmj_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%棋牌室%'::text) THEN 1 ELSE 0 END) AS qps_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%茶社%'::text) THEN 1 ELSE 0 END) AS cs_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%足疗、按摩%'::text) THEN 1 ELSE 0 END) AS zlam_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%教学培训%'::text) THEN 1 ELSE 0 END) AS jxpx_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%医疗诊所%'::text) THEN 1 ELSE 0 END) AS ylzs_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%加工生产%'::text) THEN 1 ELSE 0 END) AS jgsc_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%其它%'::text) THEN 1 ELSE 0 END) AS qt_count   
                from (select m1.* from zm_pc_base m1 ,zm_pc_yh m2 where m1.id=m2.base_id) n1 ,zm_buildings n2   
                where n1.fwbh=n2.fwbh and  cjzwqtjyytmsg is not null and  cjzwqtjyyt ='1' and n1.fwbh not in(select distinct fwbh from "+ table_fwzt + " where final_fwzt is not null and   final_create_on='"+today+" 23:59:59')  group by n1.xzq,n1.xzqdm order by n1.xzqdm) t where 1=1 " + strWhere;

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = @"select count(1)cnt from (select n1.xzq,n1.xzqdm,  sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%餐饮%'::text) THEN 1 ELSE 0 END) AS cy_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%商店%'::text) THEN 1 ELSE 0 END) AS sd_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%美容美发%'::text) THEN 1 ELSE 0 END) AS mrmf_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%KTV%'::text) THEN 1 ELSE 0 END) AS ktv_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%网吧%'::text) THEN 1 ELSE 0 END) AS wb_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%美甲美睫%'::text) THEN 1 ELSE 0 END) AS mjmj_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%棋牌室%'::text) THEN 1 ELSE 0 END) AS qps_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%茶社%'::text) THEN 1 ELSE 0 END) AS cs_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%足疗、按摩%'::text) THEN 1 ELSE 0 END) AS zlam_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%教学培训%'::text) THEN 1 ELSE 0 END) AS jxpx_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%医疗诊所%'::text) THEN 1 ELSE 0 END) AS ylzs_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%加工生产%'::text) THEN 1 ELSE 0 END) AS jgsc_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%其它%'::text) THEN 1 ELSE 0 END) AS qt_count   
                from (select m1.* from zm_pc_base m1 ,zm_pc_yh m2 where m1.id=m2.base_id) n1 ,zm_buildings n2   
                where n1.fwbh=n2.fwbh and  cjzwqtjyytmsg is not null and  cjzwqtjyyt ='1' and n1.fwbh not in(select distinct fwbh from "+ table_fwzt + " where final_fwzt is not null and   final_create_on='"+today+" 23:59:59')  group by n1.xzq,n1.xzqdm order by n1.xzqdm) t where 1=1 " + strWhere;
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });
        }


        /// <summary>
        /// 统计人员密集场所
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("static/rymjcs/townlist")]
        [Produces("application/json")]
        public RespRet<Page<object>> rymjcstownList([FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string xzqdm)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "";

            if (!string.IsNullOrEmpty(xzqdm))
            {
                strWhere += "and xzqdm ='" + xzqdm + "'";
            }

            if (user.RoleType == 4 || user.RoleType == 3)
            {
                strWhere += "and xzqdm ='" + model.RegionCode + "'";
            }
            string today = DateTime.Now.ToString("yyyy-MM-dd");
             string table_fwzt = "";

                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;

            string sql = @" select ROW_NUMBER() OVER() AS idex,* from (select t2.*,t8.region_name town from (select n1.xzq,n1.xzqdm, n2.towndm,  sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%餐饮%'::text) THEN 1 ELSE 0 END) AS cy_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%商店%'::text) THEN 1 ELSE 0 END) AS sd_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%美容美发%'::text) THEN 1 ELSE 0 END) AS mrmf_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%KTV%'::text) THEN 1 ELSE 0 END) AS ktv_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%网吧%'::text) THEN 1 ELSE 0 END) AS wb_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%美甲美睫%'::text) THEN 1 ELSE 0 END) AS mjmj_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%棋牌室%'::text) THEN 1 ELSE 0 END) AS qps_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%茶社%'::text) THEN 1 ELSE 0 END) AS cs_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%足疗、按摩%'::text) THEN 1 ELSE 0 END) AS zlam_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%教学培训%'::text) THEN 1 ELSE 0 END) AS jxpx_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%医疗诊所%'::text) THEN 1 ELSE 0 END) AS ylzs_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%加工生产%'::text) THEN 1 ELSE 0 END) AS jgsc_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%其它%'::text) THEN 1 ELSE 0 END) AS qt_count   
                from (select m1.* from zm_pc_base m1 ,zm_pc_yh m2 where m1.id=m2.base_id) n1 ,zm_buildings n2   
                where n1.fwbh=n2.fwbh and  cjzwqtjyytmsg is not null and  cjzwqtjyyt ='1' and n1.fwbh not in(select distinct fwbh from " + table_fwzt+" where final_fwzt is not null and   final_create_on='"+ today + " 23:59:59')  group by n1.xzq,n1.xzqdm, n2.towndm order by n2.towndm) t2 left join zm_department t8 on t2.towndm=t8.id) t where 1=1 " + strWhere;

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = @"select count(1)cnt from (select t2.*,t8.region_name town from (select n1.xzq,n1.xzqdm, n2.towndm,  sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%餐饮%'::text) THEN 1 ELSE 0 END) AS cy_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%商店%'::text) THEN 1 ELSE 0 END) AS sd_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%美容美发%'::text) THEN 1 ELSE 0 END) AS mrmf_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%KTV%'::text) THEN 1 ELSE 0 END) AS ktv_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%网吧%'::text) THEN 1 ELSE 0 END) AS wb_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%美甲美睫%'::text) THEN 1 ELSE 0 END) AS mjmj_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%棋牌室%'::text) THEN 1 ELSE 0 END) AS qps_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%茶社%'::text) THEN 1 ELSE 0 END) AS cs_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%足疗、按摩%'::text) THEN 1 ELSE 0 END) AS zlam_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%教学培训%'::text) THEN 1 ELSE 0 END) AS jxpx_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%医疗诊所%'::text) THEN 1 ELSE 0 END) AS ylzs_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%加工生产%'::text) THEN 1 ELSE 0 END) AS jgsc_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%其它%'::text) THEN 1 ELSE 0 END) AS qt_count   
                from (select m1.* from zm_pc_base m1 ,zm_pc_yh m2 where m1.id=m2.base_id) n1 ,zm_buildings n2   
                where n1.fwbh=n2.fwbh and  cjzwqtjyytmsg is not null and  cjzwqtjyyt ='1' and n1.fwbh not in(select distinct fwbh from " + table_fwzt+ " where final_fwzt is not null and   final_create_on='" + today + " 23:59:59')  group by n1.xzq,n1.xzqdm, n2.towndm order by n2.towndm) t2 left join zm_department t8 on t2.towndm=t8.id) t where 1=1 " + strWhere;
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });
        }

        /// <summary>
        /// 统计人员密集场所
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("static/rymjcs/villagelist")]
        [Produces("application/json")]
        public RespRet<Page<object>> rymjcsvillageList([FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string towndm)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "";

            if (!string.IsNullOrEmpty(towndm))
            {
                strWhere += "and towndm ='" + towndm + "'";
            }
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";

            DateTime endData = Convert.ToDateTime(today);
            table_fwzt = "zm_building_fwzt_" + endData.Year;

            string sql = @"select ROW_NUMBER() OVER() AS idex,* from (select t2.*,t8.region_name town,t9.region_name village from (select n1.xzq,n1.xzqdm, n2.towndm, n2.villagedm,sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%餐饮%'::text) THEN 1 ELSE 0 END) AS cy_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%商店%'::text) THEN 1 ELSE 0 END) AS sd_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%美容美发%'::text) THEN 1 ELSE 0 END) AS mrmf_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%KTV%'::text) THEN 1 ELSE 0 END) AS ktv_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%网吧%'::text) THEN 1 ELSE 0 END) AS wb_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%美甲美睫%'::text) THEN 1 ELSE 0 END) AS mjmj_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%棋牌室%'::text) THEN 1 ELSE 0 END) AS qps_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%茶社%'::text) THEN 1 ELSE 0 END) AS cs_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%足疗、按摩%'::text) THEN 1 ELSE 0 END) AS zlam_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%教学培训%'::text) THEN 1 ELSE 0 END) AS jxpx_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%医疗诊所%'::text) THEN 1 ELSE 0 END) AS ylzs_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%加工生产%'::text) THEN 1 ELSE 0 END) AS jgsc_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%其它%'::text) THEN 1 ELSE 0 END) AS qt_count   
                from (select m1.* from zm_pc_base m1 ,zm_pc_yh m2 where m1.id=m2.base_id) n1 ,zm_buildings n2   
                where n1.fwbh=n2.fwbh and  cjzwqtjyytmsg is not null and  cjzwqtjyyt ='1' and n1.fwbh not in(select distinct fwbh from " + table_fwzt + " where final_fwzt is not null and   final_create_on='" + today + " 23:59:59') group by n1.xzq,n1.xzqdm,n2.towndm,n2.villagedm order by n2.villagedm) t2" +
                " left join zm_department t8 on t2.towndm=t8.id" +
                " left join zm_department t9 on t2.villagedm=t9.id) t where 1=1 " + strWhere;

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = @"select count(1)cnt from (select t2.*,t8.region_name town,t9.region_name village from (select n1.xzq,n1.xzqdm, n2.towndm, n2.villagedm,sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%餐饮%'::text) THEN 1 ELSE 0 END) AS cy_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%商店%'::text) THEN 1 ELSE 0 END) AS sd_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%美容美发%'::text) THEN 1 ELSE 0 END) AS mrmf_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%KTV%'::text) THEN 1 ELSE 0 END) AS ktv_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%网吧%'::text) THEN 1 ELSE 0 END) AS wb_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%美甲美睫%'::text) THEN 1 ELSE 0 END) AS mjmj_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%棋牌室%'::text) THEN 1 ELSE 0 END) AS qps_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%茶社%'::text) THEN 1 ELSE 0 END) AS cs_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%足疗、按摩%'::text) THEN 1 ELSE 0 END) AS zlam_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%教学培训%'::text) THEN 1 ELSE 0 END) AS jxpx_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%医疗诊所%'::text) THEN 1 ELSE 0 END) AS ylzs_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%加工生产%'::text) THEN 1 ELSE 0 END) AS jgsc_count,
                 sum(CASE WHEN ((cjzwqtjyytmsg)::text ~~ '%其它%'::text) THEN 1 ELSE 0 END) AS qt_count   
                from (select m1.* from zm_pc_base m1 ,zm_pc_yh m2 where m1.id=m2.base_id) n1 ,zm_buildings n2   
                where n1.fwbh=n2.fwbh and  cjzwqtjyytmsg is not null and  cjzwqtjyyt ='1' and n1.fwbh not in(select distinct fwbh from " + table_fwzt + " where final_fwzt is not null and   final_create_on='" + today + " 23:59:59') group by n1.xzq,n1.xzqdm,n2.towndm,n2.villagedm order by n2.villagedm) t2" +
                " left join zm_department t8 on t2.towndm=t8.id" +
                " left join zm_department t9 on t2.villagedm=t9.id) t where 1=1 " + strWhere;
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });
        }

    }

}




